package cc.messcat.dao.fupin;

import cc.messcat.common.dao.BaseDaoImpl;
import cc.messcat.common.model.Pager;
import cc.messcat.common.util.BeanUtil;
import cc.messcat.common.util.StringUtil;
import cc.messcat.dao.area.AreaDao;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.fupin.PoorVillage;
import cc.messcat.entity.fupin.PoorVillageHistory;
import cc.messcat.entity.system.ApplicationProperties;
import cc.messcat.entity.system.McUser;
import cc.messcat.service.area.AreaService;
import cc.messcat.util.Collections;
import cc.messcat.util.LevelUtil;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.NumberFormat;
import java.util.*;

@Repository("poorViDao")
public class PoorVillageDaoImpl extends BaseDaoImpl<PoorVillage> implements PoorVillageDao {
	
	  @Autowired
	  private AreaDao areaDao;
	  
	  @Autowired
	  private AreaService areaService;

    @Override
    public PoorVillage getVillageByAreaId(Long areaId) {
        Query query = getCurrentSession().createQuery("from PoorVillage pv where pv.isDel=0 and pv.area.id = :areaId");
        query.setLong("areaId", areaId);
        List result = query.list();
        if (BeanUtil.isValid(result)) {
            return (PoorVillage) result.get(0);
           
        }
        return null;
    }

    @Override
    public List<PoorVillage> getVillagesByCondition(PoorVillage poorVillage) {
        Criteria criteria = getCurrentSession().createCriteria(PoorVillage.class);
        if (BeanUtil.isValid(poorVillage)) {
            if (BeanUtil.isValid(poorVillage.getProvince()))
                criteria.add(Restrictions.eq("province", poorVillage.getProvince()));
            if (BeanUtil.isValid(poorVillage.getCity()))
                criteria.add(Restrictions.eq("city", poorVillage.getCity()));
            if (BeanUtil.isValid(poorVillage.getCry()))
                criteria.add(Restrictions.eq("cry", poorVillage.getCry()));
            if (BeanUtil.isValid(poorVillage.getCty()))
                criteria.add(Restrictions.eq("cty", poorVillage.getCty()));
            if (BeanUtil.isValid(poorVillage.getCty()))
                criteria.add(Restrictions.eq("name", poorVillage.getName()));
        }
        return criteria.list();
    }

    @Override
    public Date getAddTimeById(Long id) {
        if (id != null) {
            String hql = "select addTime from PoorVillage where id = " + id;
            Query query = this.getCurrentSession().createQuery(hql);
            Object obj = query.uniqueResult();
            if (obj != null)
                return (Date) query.uniqueResult();
        }
        return null;
    }

    @Override
    public PoorVillage getVillageByAreaIdWithClear(Long id,String annual) {

        List result = this.getCurrentSession().createQuery("from PoorVillage where area.id = :areaId  and isDel='0' and annual= :annual")
        	.setLong("areaId", id)
        	.setString("annual", annual).list();
        if (result.size() != 0) {
            this.getCurrentSession().clear();
            return (PoorVillage) result.get(0);
        }
        return null;
    }

    @Override
    public boolean isExsitVillage(Long id,String annual) {
        if (this.getCurrentSession().createSQLQuery("select id from poor_village where AREA_ID = :areaId and IS_DEL = '0' and annual = :annual").setString("annual",annual).setLong("areaId", id).list().size() == 0)
            return false;
        return true;

    }
    
    
	/**
	 * 贫困村识别标准统计
	 */
	@Override
	@SuppressWarnings("all")
	public Pager poorVillageStatistics(Integer pageNo, Integer pageSize, String year, String country, String village, String status) {

        boolean isCountry = StringUtils.isBlank(country);
        String getDistrict = isCountry
                //获取镇
                ? "select new Map(a1.areaCode as areaCode, a1.area as area) from Area as a1 where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')"
                //获取村
		        : " select new Map(a.id as id,areaCode as areaCode,a.area as area) from Area as a where a.fatherId=(select a1.id from Area as a1 where a1.areaCode='"+ country +"')";
		
		List result = new ArrayList<>();
		String area = "";
		String areaCode = "";
		String nature = "";
		Map map = null;
		Map temp = null;
		for(Map e : (List<Map>)findPageObjects(getDistrict,pageNo,pageSize,new HashMap<>())) {
			areaCode = e.get("areaCode") != null ? e.get("areaCode").toString() : "none";
			//统计各镇结果
			String sql = "SELECT pv.NATURE as nature, count(1) as count,t1.total as total ,"
				+ " CONCAT(cast(count(1)/ t1.total*100 as DECIMAL(22,2)),'%')  as percentage "
				+ " from poor_village as pv, (select count(1) as total from poor_village as pv2,area as a2 "
				+ " where pv2.IS_DEL='0' and pv2.type ='0' "
				+ (StringUtils.isNotBlank(status) ? " and pv2.STATUS="+status : " ")
				+ (StringUtils.isNotBlank(year) ? " and pv2.annual='" + year.trim() + "'" : " ")
				+" and pv2.AREA_ID=a2.ID and a2.areaCode like '"
				+ (areaCode.length() > 8 ? isCountry ? areaCode.substring(0,9) : areaCode : "none") +"%') as t1 "
				+ " where pv.id in (select id from poor_village as pv "
				+ " where pv.IS_DEL='0' and pv.type ='0' " 
				+ (StringUtils.isNotBlank(status) ? " and pv.STATUS="+status : " ")
				+ (StringUtils.isNotBlank(year) ? " and pv.annual='" + year.trim() + "'" : " ")
				+ " and pv.AREA_ID IN (SELECT id from area where areaCode LIKE '"
				+ (areaCode.length() > 8 ? isCountry ? areaCode.substring(0,9) : areaCode : "none") +"%')) GROUP BY pv.NATURE";
			Query query = getCurrentSession().createSQLQuery(sql);
			List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			
			temp = new LinkedHashMap<>();
			temp.put("pkCount", 0);
			temp.put("pkPercentage", "0%");
			temp.put("jjbrCount", 0);
			temp.put("jjbrPercentage", "0%");
			temp.put("sewCount", 0);
			temp.put("sewPercentage", "0%");
			temp.put("sswCount", 0);
			temp.put("sswPercentage", "0%");
			for(Map r : (List<Map>)list) {
				switch(StringUtils.isBlank((String) r.get("nature")) ? "others" :  r.get("nature").toString().trim()) {
					case "贫困村" :
						temp.put("pkCount", r.get("count").toString());
						temp.put("pkPercentage", r.get("percentage").toString());
						break;
					case "经济薄弱村" :
						temp.put("jjbrCount", r.get("count").toString());
						temp.put("jjbrPercentage", r.get("percentage").toString());
						break;
					case "十二五贫困村" :
						temp.put("sewCount", r.get("count").toString());
						temp.put("sewPercentage", r.get("percentage").toString());
						break;
					case "十三五贫困村" :
						temp.put("sswCount", r.get("count").toString());
						temp.put("sswPercentage", r.get("percentage").toString());
						break;
					case "others" :
						temp.put("othersCount", r.get("count").toString());
						temp.put("othersPercentage", r.get("percentage").toString());
						break;
				}
			}
			
			map = new HashMap<>();
			map.put("area", e.get("area"));
			map.put("totalPoorVillage",list.isEmpty() ? "0" : ((Map)list.get(0)).get("total"));
			map.put("verbose",temp);
			result.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(getDistrict,null).size(),result);
	}

	@Override
	@SuppressWarnings("all")
	public Pager poorVilageDevelopmentclass(Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
        boolean isCountry = StringUtils.isBlank(country);
        String getDistrict = isCountry
                //获取镇
                ? "select new Map(a1.areaCode as areaCode, a1.area as area) from Area as a1 where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')"
                //获取村
                : " select new Map(a.id as id,areaCode as areaCode,a.area as area) from Area as a where a.fatherId=(select a1.id from Area as a1 where a1.areaCode='" + country + "')";

        List result = new ArrayList<>();
        String area = "";
        String areaCode = "";
        Map map = null;
        Map temp = null;
        for(Map e : (List<Map>)findPageObjects(getDistrict,pageNo,pageSize,new HashMap<>())) {
            areaCode = e.get("areaCode") != null ? e.get("areaCode").toString() : "none";
            //统计各镇结果
            String sql = "SELECT pv.DEVELOPMENT as development, count(1) as count,t1.total as total ,"
                + " CONCAT(cast(count(1)/ t1.total*100 as DECIMAL(22,2)),'%')  as percentage "
                + " from poor_village as pv, (select count(1) as total from poor_village as pv2,area as a2 "
                + " where pv2.TYPE = '0' AND pv2.IS_DEL = '0' "+(StringUtils.isNotBlank(status) ? " and pv2.status="+status : " ")
                + (StringUtils.isNotBlank(year) ? " and pv2.annual='" + year.trim() + "'" : " ")
                +" and pv2.AREA_ID=a2.ID and a2.areaCode like '"+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none" : areaCode) + "') as t1 "
                + " where pv.id in (select id from poor_village as pv "
                + " where pv.TYPE = '0' AND pv.IS_DEL = '0' " +(StringUtils.isNotBlank(status) ? " and pv.status="+status : " ")
                + (StringUtils.isNotBlank(year) ? " and pv.annual='" + year.trim() + "'" : " ")
                + " and pv.AREA_ID IN (SELECT id from area where IS_DEL='0' and areaCode LIKE '"+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none"  : areaCode) +"')) GROUP BY pv.DEVELOPMENT";
            Query query = getCurrentSession().createSQLQuery(sql);
            List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
            Integer b = 0;
            Integer c = 0;
            Integer d = 0;
            Integer f = 0;
            temp = new LinkedHashMap<>();
            temp.put("zxcCount", 0);//中心村镇数
            temp.put("zxcPercentage","0%");//中心村镇占比
            temp.put("ybcCount", 0);//一般村镇数
            temp.put("ybcPercentage","0%");//一般村镇占比
            temp.put("gdcCount", 0);//过渡村镇数
            temp.put("gdcPercentage", "0%");//过渡村镇占比
            String percentage = "";
            for(Map r : (List<Map>)list) {
                switch(StringUtils.isBlank((String) r.get("development")) ? "others" :  r.get("development").toString().trim()) {
                    case "中心村镇" :
                        //b++;
                        temp.put("zxcCount",r.get("count").toString());
                        percentage = r.get("percentage").toString();
                        temp.put("zxcPercentage", "0.00%".equals(percentage) ? "0%" : percentage);
                        break;
                    case "一般村镇" :
                        //c++;
                        temp.put("ybcCount",r.get("count").toString());
                        percentage = r.get("percentage").toString();
                        temp.put("ybcPercentage", "0.00%".equals(percentage) ? "0%" : percentage);
                        break;
                    case "过渡村镇" :
                        //d++;
                        temp.put("gdcCount",r.get("count").toString());
                        percentage = r.get("percentage").toString();
                        temp.put("gdcPercentage", "0.00%".equals(percentage) ? "0%" : percentage);
                        break;
                    case "others" :
                        //f++;
                        temp.put("othersCount", r.get("count").toString());
                        percentage = r.get("percentage").toString();
                        temp.put("othersPercentage", "0.00%".equals(percentage) ? "0%" : percentage);
                        break;
                }

            }

            map = new HashMap<>();
            map.put("area", e.get("area"));
            map.put("totalPoorVillage",list.isEmpty() ? "0" : ((Map)list.get(0)).get("total"));
            map.put("verbose",temp);
            result.add(map);
        }
        return new Pager(pageSize,pageNo,queryList(getDistrict,null).size(),result);
				
	}

	@Override
	@SuppressWarnings("all")
	public Pager poorVilageLandformclass(Integer pageNo, Integer pageSize, String year, String country, String village,String status) {
        boolean isCountry = StringUtils.isBlank(country);
        String getDistrict = isCountry
                //获取镇
                ? "select new Map(a1.areaCode as areaCode, a1.area as area) from Area as a1 where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')"
                //获取村
                : " select new Map(a.id as id,areaCode as areaCode,a.area as area) from Area as a where a.fatherId=(select a1.id from Area as a1 where a1.areaCode='" + country + "')";

        List result = new ArrayList<>();
		String area = "";
		String areaCode = "";
		Map map = null;
		Map temp = null;
		for(Map e : (List<Map>)findPageObjects(getDistrict,pageNo,pageSize,new HashMap<>())) {
			areaCode = e.get("areaCode") != null ? e.get("areaCode").toString() : "none";
			//统计各镇结果
			String sql = "SELECT pv.TERRAIN as terrain, count(1) as count,t1.total as total ,"
				+ " CONCAT(cast(count(1)/ t1.total*100 as DECIMAL(22,2)),'%')  as percentage "
				+ " from poor_village as pv, (select count(1) as total from poor_village as pv2,area as a2 "
				+ " where pv2.TYPE = '0' and pv2.IS_DEL = '0' "+(StringUtils.isNotBlank(status) ? " and pv2.status="+status : " ")
				+ (StringUtils.isNotBlank(year) ? " and pv2.annual='" + year.trim() + "'" : " ")
				+" and pv2.AREA_ID=a2.ID and a2.areaCode like '"+ (isCountry ? areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none"  : areaCode) + "') as t1 "
				+ " where pv.id in (select id from poor_village as pv "
				+ " where pv.TYPE = '0' and pv.IS_DEL = '0' " +(StringUtils.isNotBlank(status) ? " and pv.status="+status : " ")
				+ (StringUtils.isNotBlank(year) ? " and pv.annual='" + year.trim() + "'" : " ")
				+ " and pv.AREA_ID IN (SELECT id from area where IS_DEL='0' and areaCode LIKE '"+(isCountry ? areaCode.length() > 8 ? areaCode.substring(0,9)+"%" : "none" : areaCode)+"')) GROUP BY pv.TERRAIN";
			Query query = getCurrentSession().createSQLQuery(sql);
			List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			
			temp = new LinkedHashMap<>();
			temp.put("sqCount", 0);//山区村镇
			temp.put("sqPercentage", "0%");//山区村镇占比
			temp.put("qlCount", 0);//丘陵
			temp.put("qlPercentage", "0%");//丘陵占比
			temp.put("pbCount", 0);//平坝村镇
			temp.put("pbPercentage", "0%");//平坝村镇占比
            String percentage = "";
			for(Map r : (List<Map>)list) {
				switch(StringUtils.isBlank((String) r.get("terrain")) ? "others" :  r.get("terrain").toString().trim()) {
					case "山区村镇" :
						temp.put("sqCount", r.get("count").toString());
						percentage = r.get("percentage").toString();
						temp.put("sqPercentage",percentage);
						break;
					case "丘陵" :
						temp.put("qlCount", r.get("count").toString());
                        percentage = r.get("percentage").toString();
						temp.put("qlPercentage", percentage);
						break;
					case "平坝村镇" :
						temp.put("pbCount", r.get("count").toString());
                        percentage = r.get("percentage").toString();
						temp.put("pbPercentage", percentage);
						break;
					case "others" :
						temp.put("othersCount", r.get("count").toString());
                        percentage = r.get("percentage").toString();
						temp.put("othersPercentage",percentage);
						break;
				}
				
			}
			
			map = new HashMap<>();
			map.put("area", e.get("area"));
			map.put("totalPoorVillage",list.isEmpty() ? "0" : ((Map)list.get(0)).get("total"));
			map.put("verbose",temp);
			result.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(getDistrict,null).size(),result);
	}

	@Override
	@SuppressWarnings("all")
	public Pager ruralTourism(McUser user,Integer pageNo, Integer pageSize, String year, String country, String village,String status) {
		// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}

				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";

				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}

				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
					
					// 县级查询则统计镇，镇级查询则统计村
					areaCode = isCounty 
							? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
							: ((Map) e).get("areaCode").toString().trim();
					System.out.println(((Map) e).get("areaCode").toString().trim());
					
					laborTypeCount = getCurrentSession().createSQLQuery("SELECT  "+( isCounty ? "p.cry" : "p.village")+" as region,"
                            + " (select  sum(if(ifnull(po.tourism,0)>0,1,0)) as count FROM poor_village po,area as a WHERE a.id=po.area_id and po.type = '0' and po.is_del='0' and  po.tourism is not null and "+( isCounty ? "p.cry = po.cry" : "p.area_id = po.area_id ")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS a,"
                            +"(SELECT COALESCE(SUM(po.tourism),0) FROM poor_village po,area as a  WHERE a.id=po.area_id and  po.type = '0' and po.is_del='0' and  po.tourism is not null and "+( isCounty ? "p.cry = po.cry" : "p.area_id = po.area_id ")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS b,"
                            +"(SELECT  COALESCE(SUM(po.farm),0) FROM poor_village po,area as a  WHERE a.id=po.area_id and  po.type = '0' and po.is_del='0' and  po.farm IS NOT NULL and "+( isCounty ? "p.cry = po.cry" : "p.area_id = po.area_id")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS c,"
                            +"(SELECT  COALESCE(SUM(po.job),0) FROM poor_village po,area as a  WHERE a.id=po.area_id and  po.type = '0' and po.is_del='0' and  "+( isCounty ? "p.cry = po.cry" : "p.area_id = po.area_id")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS d ,"
                            +"(SELECT  COALESCE(SUM(po.farm_income),0) FROM poor_village  po,area as a WHERE  a.id=po.area_id and  po.type = '0' and po.is_del='0' and "+( isCounty ? "p.cry = po.cry" : "p.area_id = po.area_id")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS e"
                            + " FROM poor_village as p,area as a  WHERE a.id=p.area_id and  p.type = '0' and p.is_del='0' and a.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(status) ? " and p.status="+status : " ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "p.cry" : "p.area_id")+"")
                            .setFirstResult(pageSize*(pageNo-1)).setMaxResults(pageSize)
                            .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
					Map map = new LinkedHashMap<>();
					for (Object o : laborTypeCount) {
						typeVerbose = new HashMap<>();
						Object totalVillage = ((Map) o).get ("a");
						typeVerbose.put("poorTourism", totalVillage!=null? new Double(totalVillage.toString()) : "0");//开展乡村旅游的贫困村数
						typeVerbose.put("tourismHouseholds", (((Map) o).get("b"))!=null? ((Map) o).get("b") : "0");//开展乡村旅游的户数
						typeVerbose.put("agritainment",(((Map) o).get("c"))!=null? ((Map) o).get("c") : "0");//农家乐经营户数
						typeVerbose.put("ruralTourism", (((Map) o).get("d"))!=null? ((Map) o).get("d") : "0");//乡村旅游从业人数
						typeVerbose.put("annualIncome", (((Map) o).get("e"))!=null? ((Map) o).get("e") : "0");//经营农家乐户数年均收入
						map.put("verbose", typeVerbose);
					}
					map.put("region", ((Map) e).get("area"));	//地区
					
					list.add(map);
				}
				return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	@SuppressWarnings("all")
	@Override
	public Pager satelliteMonitor(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		// 搜索条件
		// 如果没有设置乡镇，默认县下所有乡镇
		String areaCondition = "130434000000";
		boolean isCounty = true;
		// 如果有，验证权限，镇级以上，则查询该镇下所有村
		if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			System.out.println(">>>> 镇级以上");
			areaCondition = country.trim();
			isCounty = false;
		}
		// 如果镇级，则当前用户所在镇下所有村
		if (user.getUserLevel().getLevel() == 4) {
			System.out.println(">>>> 镇级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			isCounty = false;
		}

		String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
			+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
			+ "') order by areaCode";

		// 村级
		if (user.getUserLevel().getLevel() == 5) {
			System.out.println(">>>> 村级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			// 重置地区
			grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
				+ areaCondition + "'";
		}

		Map<String, Object> verbose = null;
		Map<String, Object> typeVerbose = null;
		List laborTypeCount = new ArrayList<>();
		List list = new ArrayList<>();
		Long count = 0L;
		String areaCode = "";
		String areaCe = "";
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
				
			// 县级查询则统计镇，镇级查询则统计村
			areaCode = isCounty 
					? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
					: ((Map) e).get("areaCode").toString().trim();
			System.out.println(((Map) e).get("areaCode").toString().trim());
			
			laborTypeCount = this.find("SELECT new map( "+( isCounty ? "p.cry" : "p.village")+" as region , COUNT(*) AS a ,COALESCE(SUM(p.hygiene),0) AS b ,SUM(p.toilet) AS c ,COALESCE(SUM(p.garbage),0) AS d ,"	
			
			+"(SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.hygiene = '0' OR po.hygiene IS NULL) and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS e ,"
			+"ROUND((SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.hygiene = '0' OR po.hygiene IS NULL) and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+")/COALESCE(SUM(p.hygiene),0),2) AS f ,"
			+"(SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.toilet = '0' OR po.toilet IS NULL) and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS g ,"
			+"ROUND((SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.toilet = '0' OR po.toilet IS NULL) and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+")/COALESCE(SUM(p.toilet),0)) AS h ,"
			+"(SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.garbage = '0' OR po.garbage IS NULL) and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS i,"
			+"ROUND((SELECT COUNT(*) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' AND (po.garbage = '0' OR po.garbage IS NULL) and " +( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ") + ")/COALESCE(SUM(p.garbage),0)) AS j ,"
			+"COALESCE(SUM(p.doctor),0) AS k,ROUND((COALESCE(SUM(p.doctor),0)/1000),2) AS l )"
			+ " FROM PoorVillage as p WHERE p.type = '0' and p.isDel='0' and p.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(status) ? " and p.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "p.cry" : "p.area.id")+"");
			
			Map map = new LinkedHashMap<>();
			typeVerbose = new HashMap<>();
			typeVerbose.put("povertyVillages","0");//总贫困村数
			typeVerbose.put("healthRooms","0");//卫生室个数
			typeVerbose.put("toiletsPublic","0");//公共卫生厕所个数
			typeVerbose.put("numberGarbage","0");//垃圾集中堆放点个数
			typeVerbose.put("poorVillages","0");//无卫生室贫困村.村数
			typeVerbose.put("nopoorVillage","0%");//无卫生室贫困村.占比
			typeVerbose.put("healthToilets","0");//无公共卫生厕所贫困村.村数
			typeVerbose.put("healthToiletsProportion", "0%");//无公共卫生厕所贫困村.占比
			typeVerbose.put("garbagecollection","0");//无垃圾集中堆放点贫困村.村数
			typeVerbose.put("garbagecollectionProportion","0%");//无公共卫生厕所贫困村.占比
			typeVerbose.put("physicians","0");//执业（助理）医师数
			typeVerbose.put("onethousandPhysicians","0");//每千人执业（助理）医师数
			
			for (Object o : laborTypeCount) {
				String a = ((Map) o).get("a").toString() == null ?"0":((Map) o).get("a").toString();
				String f = "0";
				if(StringUtil.isNotBlank(a)&&!a.equals("0")){
					f = BigDecimal.valueOf(StringUtil.isBlank(((Map) o).get("e").toString())?0:Long.valueOf(((Map) o).get("e").toString())).divide( BigDecimal.valueOf(Long.valueOf(a)), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					}
				if(f.equals("0")||"0"==f||f.equals("0.0%")||"0.0%"==f){
					f="0%";
				}
				String a2 = ((Map) o).get("a").toString() == null ?"0":((Map) o).get("a").toString();
				String g = "0";
				if(StringUtil.isNotBlank(a2)&&!a2.equals("0")){
					g = BigDecimal.valueOf(StringUtil.isBlank(((Map) o).get("g").toString())?0:Long.valueOf(((Map) o).get("g").toString())).divide( BigDecimal.valueOf(Long.valueOf(a2)), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					}
				if(g.equals("0")||"0"==g||g.equals("0.0%")||"0.0%"==g){
					g="0%";
				}
				String a3 = ((Map) o).get("a").toString() == null ?"0":((Map) o).get("a").toString();
				String i = "0";
				if(StringUtil.isNotBlank(a3)&&!a3.equals("0")){
					i = BigDecimal.valueOf(StringUtil.isBlank(((Map) o).get("i").toString())?0:Long.valueOf(((Map) o).get("i").toString())).divide( BigDecimal.valueOf(Long.valueOf(a3)), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					}
				if(i.equals("0")||"0"==i||i.equals("0.0%")||"0.0%"==i){
					i="0%";
				}
				String k = ((Map) o).get("k").toString();
				 k = BigDecimal.valueOf(Long.valueOf(((Map) o).get("k").toString())).divide(BigDecimal.valueOf(1000), 2, BigDecimal.ROUND_HALF_UP).doubleValue()+"";
				 if(k.equals("0.0")||"0.0"==k){
						k="0";
					}
				typeVerbose.put("povertyVillages", (((Map) o).get("a"))!=null? ((Map) o).get("a") : "0");//总贫困村数
				typeVerbose.put("healthRooms", (((Map) o).get("b"))!=null? ((Map) o).get("b") : "0");//卫生室个数
				typeVerbose.put("toiletsPublic", (((Map) o).get("c"))!=null? ((Map) o).get("c") : "0");//公共卫生厕所个数
				typeVerbose.put("numberGarbage",(((Map) o).get("d"))!=null? ((Map) o).get("d") : "0");//垃圾集中堆放点个数
				typeVerbose.put("poorVillages", (((Map) o).get("e"))!=null? ((Map) o).get("e") : "0");//无卫生室贫困村.村数
				typeVerbose.put("nopoorVillage", f!=null? f : "0%");//无卫生室贫困村.占比
				typeVerbose.put("healthToilets", (((Map) o).get("g"))!=null? ((Map) o).get("g") : "0");//无公共卫生厕所贫困村.村数
				typeVerbose.put("healthToiletsProportion", g!=null? g : "0%");//无公共卫生厕所贫困村.占比
				typeVerbose.put("garbagecollection", (((Map) o).get("i"))!=null? ((Map) o).get("i") : "0");//无垃圾集中堆放点贫困村.村数
				typeVerbose.put("garbagecollectionProportion",i!=null? i : "0%");//无垃圾集中堆放点贫困村.占比
				typeVerbose.put("physicians", (((Map) o).get("k"))!=null? ((Map) o).get("k") : "0");//执业（助理）医师数
				typeVerbose.put("onethousandPhysicians", k!=null? k : "0");//每千人执业（助理）医师数
			}
			map.put("verbose", typeVerbose);
			map.put("region", ((Map) e).get("area"));	//地区
			list.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	/**
	  * 贫困村文化建设情况统计表
	  */
	@SuppressWarnings("all")
	@Override
	public Pager construction(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}

				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";

				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}

				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {

					// 县级查询则统计镇，镇级查询则统计村
					areaCode = isCounty
							? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
							: ((Map) e).get("areaCode").toString().trim();
					System.out.println(((Map) e).get("areaCode").toString().trim());
					laborTypeCount.clear();
					typeVerbose = null;
					laborTypeCount = this.find("SELECT new map( "+( isCounty ? "p.cry" : "p.village")+" as region , COUNT(p.nature) AS a , SUM(p.culture) as b,"
					+"(SELECT coalesce(SUM(po.culture),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and po.area.areaCode like '"
                            + areaCode + "' " + (StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ") + ")" +
                            " /convert((SELECT COUNT(po.nature) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and po.area.areaCode like '"
                            + areaCode + "'" + (StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ") + "),decimal) AS c ,"
					+"(SELECT COUNT(po.culture) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and po.area.areaCode like '" + areaCode + "' AND po.culture <> '0' AND po.culture is not null and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS d )"
					+ " FROM PoorVillage as p WHERE p.type = '0' and p.isDel='0' and p.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(status) ? " and p.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "p.cry" : "p.village")+"");
					Map map = new LinkedHashMap<>();
					typeVerbose = new HashMap<>();
					typeVerbose.put("povertyVillages","0");//总贫困村数
					typeVerbose.put("bookRooms", "0");//文化（图书）室个数
					typeVerbose.put("bookRoomsAverage", "0");//村均文化（图书）室个数
					typeVerbose.put("numberbookRooms","0");//有文化（图书）室贫困村.村数
					typeVerbose.put("bookRoomsProportion","0%");//有文化（图书）室贫困村.占比
					for (Map o : (List<Map>)laborTypeCount) {
						String a = ((Map) o).get("a").toString() == null ?"0":((Map) o).get("a").toString();
						System.out.println(a);
						Long d = (Long) o.get("d");
						String f = "0";
						if(StringUtil.isNotBlank(a)&&!a.equals("0")){
							f = String.format("%.2f", new Double(d) / new Double(a)*100) +"%";
							/*e = BigDecimal.valueOf(StringUtil.isBlank(((Map) o).get("d").toString())?0:Long.valueOf(((Map) o).get("d").toString())).divide( BigDecimal.valueOf(Long.valueOf(a)), 4, BigDecimal.ROUND_HALF_UP)
									.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";*/
							}
						if(f.equals("0")||"0"==f||f.equals("0.00%")||"0.00%"==f){
							f="0%";
						}
						Object c = ((Map) o).get("c");
						typeVerbose.put("povertyVillages", (((Map) o).get("a"))!=null? ((Map) o).get("a") : "0");//总贫困村数
						typeVerbose.put("bookRooms", (((Map) o).get("b"))!=null? ((Map) o).get("b") : "0");//文化（图书）室个数
						typeVerbose.put("bookRoomsAverage", c!=null && !"0".equals(c.toString()) ? String.format("%.2f",Double.parseDouble((c.toString()))) : "0");//村均文化（图书）室个数
						typeVerbose.put("numberbookRooms",(((Map) o).get("d"))!=null? ((Map) o).get("d") : "0");//有文化（图书）室贫困村.村数
						typeVerbose.put("bookRoomsProportion", f!=null? f : "0%");//有文化（图书）室贫困村.占比
					}
					map.put("verbose", typeVerbose);
					map.put("region", ((Map) e).get("area"));	//地区
					list.add(map);
				}
				return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	@SuppressWarnings("all")
	@Override
	public Pager information(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,String status) {
		// 搜索条件
		// 如果没有设置乡镇，默认县下所有乡镇
		String areaCondition = "130434000000";
		boolean isCounty = true;
		// 如果有，验证权限，镇级以上，则查询该镇下所有村
		if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			System.out.println(">>>> 镇级以上");
			areaCondition = country.trim();
			isCounty = false;
		}
		// 如果镇级，则当前用户所在镇下所有村
		if (user.getUserLevel().getLevel() == 4) {
			System.out.println(">>>> 镇级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			isCounty = false;
		}

		String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
			+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
			+ "') order by areaCode";

		// 村级
		if (user.getUserLevel().getLevel() == 5) {
			System.out.println(">>>> 村级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			// 重置地区
			grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
				+ areaCondition + "'";
		}

		Map<String, Object> verbose = null;
		Map<String, Object> typeVerbose = null;
		List laborTypeCount = new ArrayList<>();
		List list = new ArrayList<>();
		Long count = 0L;
		String areaCode = "";
		String areaCe = "";
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
				
			// 县级查询则统计镇，镇级查询则统计村
			areaCode = isCounty 
					? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
					: ((Map) e).get("areaCode").toString().trim();
			System.out.println(((Map) e).get("areaCode").toString().trim());
			laborTypeCount = this.find("SELECT new map( "+( isCounty ? "p.cry" : "p.village")+" as region , COUNT(p.nature) AS a , SUM(p.total) as d,"	
			+"(SELECT COALESCE(SUM(po.total),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and  "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS b ,"
			+"(SELECT COALESCE(SUM(po.broadband),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS c ,"
			+"(SELECT COALESCE(SUM(po.school),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS e ,"
			+"(SELECT COALESCE(SUM(po.internet),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS f ,"
			+"(SELECT COALESCE(SUM(po.personnel),0) FROM PoorVillage po WHERE po.type = '0' and po.isDel='0' and "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+(StringUtils.isNotBlank(status) ? " and po.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+") AS g )"
			+ " FROM PoorVillage as p WHERE p.type = '0' and p.isDel='0' and p.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(status) ? " and p.status="+status : " ")+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "p.cry" : "p.village")+"");
			Map map = new LinkedHashMap<>();
			typeVerbose = new HashMap<>();
			typeVerbose.put("povertyVillages","0");//总贫困村数
			typeVerbose.put("households","0");//总户数
			typeVerbose.put("tkdhouseholds","0");//通宽带户数.户数
			typeVerbose.put("tkdhouseholdszb", "0.00%");//通宽带户数.占比
			typeVerbose.put("tkdschool","0");//通宽带的村小学个数
			typeVerbose.put("phonehouseholds","0");//能用手机上网的户数
			typeVerbose.put("numberInformation","0");//信息员数
			for (Object o : laborTypeCount) {
				String d = ((Map) o).get("d").toString() == null ?"0":((Map) o).get("d").toString();
				String c = "0";
				if(StringUtil.isNotBlank(d)&&!d.equals("0")){
					c = BigDecimal.valueOf(StringUtil.isBlank(((Map) o).get("c").toString())?0:Long.valueOf(((Map) o).get("c").toString())).divide( BigDecimal.valueOf(Long.valueOf(d)), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					}
				if(c.equals("0")||"0"==c||c.equals("0.0%")||"0.0%"==c){
					c="0.00%";
				}
				typeVerbose.put("povertyVillages", (((Map) o).get("a"))!=null? ((Map) o).get("a") : "0");//总贫困村数
				typeVerbose.put("households", (((Map) o).get("b"))!=null? ((Map) o).get("b") : "0");//总户数
				typeVerbose.put("tkdhouseholds", (((Map) o).get("c"))!=null? ((Map) o).get("c") : "0");//通宽带户数.户数
				typeVerbose.put("tkdhouseholdszb",c!=null? c : "0.00%");//通宽带户数.占比
				typeVerbose.put("tkdschool", (((Map) o).get("e"))!=null? ((Map) o).get("e") : "0");//通宽带的村小学个数
				typeVerbose.put("phonehouseholds", (((Map) o).get("f"))!=null? ((Map) o).get("f") : "0");//能用手机上网的户数
				typeVerbose.put("numberInformation", (((Map) o).get("g"))!=null? ((Map) o).get("g") : "0");//信息员数
			}
			map.put("verbose", typeVerbose);
			map.put("region", ((Map) e).get("area"));	//地区
			list.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}


	
	/**
	  * 证件号码重复统计表
	  */
	@Override
	public Pager duplicateIDnumber(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village) {
				// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}
				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";
				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}
				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				Area area = areaDao.getAreaByCode(country);
				if(StringUtils.isNotBlank(country)){
	            	String name = area.getArea(); 
	            	laborTypeCount = this.find("SELECT new map(f.village AS region ,"
						+"(SELECT COUNT(*) FROM Family fy WHERE fy.isDel='0' and fy.idNumber IN(SELECT fy.idNumber FROM Family fy GROUP BY fy.idNumber HAVING COUNT(fy.idNumber) >1) AND f.village = fy.village) AS a ) FROM Family f WHERE f.village = '"+name+"' and f.isDel='0'  GROUP BY f.village");
				}else {
					laborTypeCount = this.find("SELECT new map(f.village AS region ,"
							+"(SELECT COUNT(*) FROM Family fy WHERE fy.isDel='0' and fy.idNumber IN(SELECT fy.idNumber FROM Family fy GROUP BY fy.idNumber HAVING COUNT(fy.idNumber) >1) AND f.village = fy.village) AS a ) FROM Family f WHERE  f.isDel='0'  GROUP BY f.village");
				}
					// 县级查询则统计镇，镇级查询则统计村
					for (Object o : laborTypeCount) {
						typeVerbose = new HashMap<>();
						typeVerbose.put("region", ((Map) o).get("region"));	//地区
						typeVerbose.put("povertyVillages", ((Map) o).get("a"));//证件号码重复数量
						list.add(typeVerbose);
					}
					return new Pager(pageSize,pageNo,list.size(),list);
	}


	@Override
	public void merge(PoorVillage poorVillage) {
		getCurrentSession().merge(poorVillage);
	}

	/**
	 * 贫困村人口状况统计
	 */
	@Override
	@SuppressWarnings("all")
	public Pager pvcStatistics(McUser user, Integer pageNo, Integer pageSize, String annual, String country, String village,
		String status) {
		String year = StringUtils.isNotBlank(annual) ? " and pv.ANNUAL='" + annual + "'" : " ";
		String pfYear = StringUtils.isNotBlank(annual) ? " and pf.ANNUAL='" + annual + "'" : " ";
		status = StringUtils.isNotBlank(status) ? " and pv.STATUS='" + status + "'" : " ";
		
		//当前登录用户所在区域
		Area userArea = user.getArea();
		//等级
		Integer level = user.getUserLevel().getLevel();
		
		List<Map> areaList = new ArrayList<>(2);
		Integer rowCount = 0;
		
		List<String> areaCodes = new ArrayList<>();
		//镇以上权限
		if(level < LevelUtil.TOWN) {
			Map map  = getDistrict(pageNo,pageSize,StringUtils.isNotBlank(village) 
				? village 
				: StringUtils.isBlank(country) ? "" : country.length() > 8 ? country.substring(0,9)+"%" :"none");
			areaList = (List<Map>) map.get("list");
			rowCount = (Integer) map.get("rows");
		}

		//村，镇级权限
		if(level >= LevelUtil.TOWN) {
			if(userArea == null) {
				return new Pager(pageSize,pageNo,0,null);
			}
			Map temp = new HashMap<>();
			temp.put("area", find("select area from Area as a where a.id = (select a1.fatherId from Area as a1 where a1.areaCode='"+userArea.getAreaCode()+"')").get(0));
			temp.put("areaCode", userArea.getAreaCode());
			areaList.add(temp);
			rowCount = 1;
		}

		List<Map> result = new ArrayList<>();
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for(Map area : areaList) {
			String areaCode = (String) area.get("areaCode");
			if(StringUtils.isBlank(country)) {
				areaCode = areaCode.length() > 8 ? " and a.areaCode like '" +areaCode.substring(0,9) + "%'" : " and a.areaCode like 'none'";
			} else {
				areaCode = " and a.areaCode like '"+ areaCode +"' ";
			}
			String sql = "SELECT (SELECT count(1) FROM poor_village as pv, area as a where pv.IS_DEL='0' and a.id=pv.AREA_ID"+ status + year + areaCode +") as totalVillage,"
				+ " (SELECT count(1) FROM poor_village as pv, area as a where pv.IS_DEL='0' and a.id=pv.AREA_ID "+ status + year + areaCode +" and pv.type='0') as totalPoorVillage,"
				+ " COALESCE(sum(pv.TOTAL),0) as totalFamily,"
				+ " COALESCE(sum(pv.POOR),0)  as totalPoorFamily,"
				+ " COALESCE(sum(pv.TOTAL_NUMBER),0) as totalPeople,"
				+ " COALESCE(sum(pv.POOR_NUMBER),0)  as totalPoorPeople,"
				+ " COALESCE(sum(pv.FIVE_HOUSE),0) as fiveHouse,COALESCE(sum(FIVE_NUMBER),0) as fiveNumber, COALESCE(sum(pv.LOW_HOUSE),0) as lowHouse,COALESCE(sum(LOW_NUMBER),0) as lowNumber, "
				+ " COALESCE(sum(pv.LESS_NUMBER),0) as lessNumber, COALESCE(sum(pv.WOMAN_NUMBER),0) as womanNumber,COALESCE(sum(REMNANT_NUMBER),0) as remnantNumber,COALESCE(sum(pv.LABOUR_NUMBER),0) as labourNumber,"
				+ " COALESCE(sum(GOOUT_NUMBER),0) as gooutNumber"
				+ " FROM poor_village as pv, area as a "
				+ " WHERE pv.IS_DEL='0' and pv.TYPE='0' and pv.AREA_ID = a.ID "+ status + year +areaCode +"";
			Query query = getCurrentSession().createSQLQuery(sql);
			List<Map> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			for(Map e : list) {
				e.put("fiveHouse",((Double)e.get("fiveHouse")).intValue());
				e.put("fiveNumber",((Double)e.get("fiveNumber")).intValue());
				e.put("lowHouse",((Double)e.get("lowHouse")).intValue());
				e.put("lowNumber",((Double)e.get("lowNumber")).intValue());
				e.put("lessNumber",((Double)e.get("lessNumber")).intValue());
				e.put("womanNumber",((Double)e.get("womanNumber")).intValue());
				e.put("remnantNumber",((Double)e.get("remnantNumber")).intValue());
				e.put("labourNumber",((Double)e.get("labourNumber")).intValue());
				e.put("gooutNumber",((Double)e.get("gooutNumber")).intValue());
				Double totalPeople = ((Double)e.get("totalPeople"));
				Double totalPoorPeople = ((Double)e.get("totalPoorPeople"));
				e.put("probability", formatter.format(totalPeople == 0 ? 0 : totalPoorPeople / totalPeople));
				e.put("area", area.get("area"));
				result.add(e);
			}
		}
		
		return new Pager(pageSize,pageNo,rowCount,result);
	}
	
	
	/**
	 * 区域获取
	 * @return
	 */
	@SuppressWarnings("all")
	private Map getDistrict(Integer pageNo,Integer pageSize,String areaCodeCondition,String... areaCodes) {
		//获取所有镇
		if(StringUtils.isBlank(areaCodeCondition) && (areaCodes == null || areaCodes.length==0) ) {
			Map result = new HashMap<>();
			String hql = "select new Map(a1.id as id,  a1.fatherId as fatherId ,a1.area as area, a1.areaCode as areaCode) from Area as a1 "
				+ " where a1.fatherId = (select a.id from Area as a where a.areaCode = '130434000000')  order by a1.areaCode";
			List<Map> list = findPageObjects(hql, pageNo, pageSize, new HashMap<>());
			Integer rows = queryList(hql,new HashMap<>()).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		
		if(StringUtils.isNotBlank(areaCodeCondition)) {
			//获取指定镇或该镇所有村
			Map condition = new HashMap<>();
			Map result = new HashMap<>();
			condition.put("areaCondition", areaCodeCondition);
			String hql = "select new Map(a.id as id,  a.fatherId as fatherId, a.area as area, a.areaCode as areaCode)"
				+ " from Area as a where a.fatherId != 3 and a.areaCode like :areaCondition order by a.areaCode";
			List<Map> list = findPageObjects(hql,pageNo,pageSize,condition);
			Integer rows = queryList(hql,condition).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		
		if(StringUtils.isBlank(areaCodeCondition) && areaCodes != null && areaCodes.length > 0) {
			Map result = new HashMap<>();
			StringBuilder getArea = new StringBuilder("select new Map(a.id as id,  a.fatherId as fatherId, a.area as area, a.areaCode as areaCode)"
				+ " from Area as a where a.fatherId != 3 ");
			for(String areaCode : areaCodes) {
				getArea.append(" and a.areaCode like '" + areaCode + "'");
			}
			List list = findPageObjects(getArea.toString(),pageNo,pageSize,new HashMap<>());
			Integer rows = queryList(getArea.toString(),new HashMap<>()).size();
			result.put("list", list);
			result.put("rows", rows);
			return result;
		}
		return new HashMap<>(0);
	}


/**
 * 贫困村土地资源情况统计表
 */
@SuppressWarnings("all")
@Override
public Pager poorVilageLand(McUser user, Integer pageNo, Integer pageSize, String year, String country,
		String village, String type) {
			// 搜索条件
			// 如果没有设置乡镇，默认县下所有乡镇
			String areaCondition = "130434000000";
			boolean isCounty = true;
			// 如果有，验证权限，镇级以上，则查询该镇下所有村
			if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
				System.out.println(">>>> 镇级以上");
				areaCondition = country.trim();
				isCounty = false;
			}
			// 如果镇级，则当前用户所在镇下所有村
			if (user.getUserLevel().getLevel() == 4) {
				System.out.println(">>>> 镇级。》》");
				Area area = user.getArea();
				areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
				isCounty = false;
			}

			String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
				+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
				+ "') order by areaCode";

			// 村级
			if (user.getUserLevel().getLevel() == 5) {
				System.out.println(">>>> 村级。》》");
				Area area = user.getArea();
				areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
				// 重置地区
				grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
					+ areaCondition + "'";
			}

			Map<String, Object> verbose = null;
			Map<String, Object> typeVerbose = null;
			List laborTypeCount = new ArrayList<>();
			List list = new ArrayList<>();
			Long count = 0L;
			String areaCode = "";
			String areaCe = "";
		
			NumberFormat formatter = NumberFormat.getPercentInstance();
			formatter.setMinimumFractionDigits(2);
			for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
					
				// 县级查询则统计镇，镇级查询则统计村
				areaCode = isCounty 
						? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
						: ((Map) e).get("areaCode").toString().trim();
				System.out.println(((Map) e).get("areaCode").toString().trim());
				
				laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region,(SELECT COUNT(*) FROM PoorVillage p WHERE  p.type = '0' and p.nature  <> '非贫困村' AND  "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+" "+ (StringUtils.isNotBlank(year) ? " AND p.annual='" + year.trim() + "'" : " ")+""+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND p.isDel = '0' ) as totalPV,"
						+ "SUM(po.cuedLand) as cuedLand ,SUM(po.irrigation) as irrigation ,SUM(po.woodLand) as woodLand,SUM(po.farmLand) as farmLand ,SUM(po.fruit) as fruit,"
						+ "SUM(po.forageGrass) as forageGrass ,SUM(po.waters) as waters ,"
						+ "(SUM(po.cuedLand) +SUM(po.irrigation) +SUM(po.woodLand) +SUM(po.farmLand) +SUM(po.fruit) +SUM(po.forageGrass) +SUM(po.waters) ) as totalA)"
						+ " FROM PoorVillage as po  WHERE po.type = '0' and   po.isDel = '0' and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.village")+"");
			
				Map map = new LinkedHashMap<>();
				typeVerbose = new HashMap<>();
				typeVerbose.put("totalPV","0");//总贫困村数
				typeVerbose.put("totalA","0");//总面积数
				typeVerbose.put("cuedLandTotal","0");//耕地面积.面积
				typeVerbose.put("cuedLand", "0%");//耕地面积.占比
				typeVerbose.put("irrigationTotal","0");//有效灌溉面积.面积
				typeVerbose.put("irrigation", "0%");//有效灌溉面积.占比
				typeVerbose.put("woodLandTotal","0");//林地面积.面积
				typeVerbose.put("woodLand", "0%");//林地面积.占比
				typeVerbose.put("farmLandTotal","0");//退耕还林面积.面积
				typeVerbose.put("farmLand","0%");//退耕还林面积.占比
				typeVerbose.put("fruitTotal","0");//林果面积.面积
				typeVerbose.put("fruit", "0%");//林果面积.占比
				typeVerbose.put("forageGrassTotal","0");//牧草地面积.面积
				typeVerbose.put("forageGrass", "0%");//牧草地面积.占比
				typeVerbose.put("watersTotal","0");//水域面积.面积
				typeVerbose.put("waters", "0%");//水域面积.占比
				
				for (Object o : laborTypeCount) {
					String cuedLand = "0%";
					String irrigation = "0%";
					String woodLand= "0%";
					String farmLand ="0%";
					String fruit= "0%";
					String forageGrass = "0%";
					String waters= "0%";
					String  tA = (String) ((Map) o).get("totalA")==null?"0":(String) ((Map) o).get("totalA");
					if (!tA.equals("0")&&StringUtil.isNotBlank(tA)) {
					
						cuedLand = BigDecimal.valueOf(StringUtil.isBlank((String)((Map) o).get("cuedLand"))?0:Long.valueOf((String) ((Map) o).get("cuedLand"))).divide( BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						irrigation = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("irrigation"))?0:Long.valueOf((String) ((Map) o).get("irrigation"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						woodLand = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("woodLand"))?0:Long.valueOf((String) ((Map) o).get("woodLand"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						farmLand = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("farmLand"))?0:Long.valueOf((String) ((Map) o).get("farmLand"))).divide( BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						fruit = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("fruit"))?0:Long.valueOf((String) ((Map) o).get("fruit"))).divide( BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						forageGrass = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("forageGrass"))?0:Long.valueOf((String) ((Map) o).get("forageGrass"))).divide( BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						waters = BigDecimal.valueOf( StringUtil.isBlank((String)((Map) o).get("waters"))?0:Long.valueOf((String) ((Map) o).get("waters"))).divide( BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("totalA"))), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					}
					//typeVerbose.put("region", ((Map) o).get("region"));	//地区
					typeVerbose.put("totalPV", (((Map) o).get("totalPV"))!=null? ((Map) o).get("totalPV") : "0");//总贫困村数
					typeVerbose.put("totalA", (((Map) o).get("totalA"))!=null? ((Map) o).get("totalA") : "0");//总面积数
					typeVerbose.put("cuedLandTotal",(((Map) o).get("cuedLand"))!=null? ((Map) o).get("cuedLand") : "0");//耕地面积.面积
					typeVerbose.put("cuedLand", cuedLand);//耕地面积.占比
					typeVerbose.put("irrigationTotal",(((Map) o).get("irrigation"))!=null? ((Map) o).get("irrigation") : "0");//有效灌溉面积.面积
					typeVerbose.put("irrigation", irrigation);//有效灌溉面积.占比
					typeVerbose.put("woodLandTotal", (((Map) o).get("woodLand"))!=null? ((Map) o).get("woodLand") : "0");//林地面积.面积
					typeVerbose.put("woodLand", woodLand);//林地面积.占比
					typeVerbose.put("farmLandTotal",(((Map) o).get("farmLand"))!=null? ((Map) o).get("farmLand") : "0");//退耕还林面积.面积
					typeVerbose.put("farmLand",farmLand);//退耕还林面积.占比	
					typeVerbose.put("fruitTotal", (((Map) o).get("fruit"))!=null? ((Map) o).get("fruit") : "0");//林果面积.面积
					typeVerbose.put("fruit", fruit);//林果面积.占比
					typeVerbose.put("forageGrassTotal", (((Map) o).get("forageGrass"))!=null? ((Map) o).get("forageGrass") : "0");//牧草地面积.面积
					typeVerbose.put("forageGrass", forageGrass);//牧草地面积.占比	
					typeVerbose.put("watersTotal",(((Map) o).get("waters"))!=null? ((Map) o).get("waters") : "0");//水域面积.面积
					typeVerbose.put("waters", waters);//水域面积.占比
				}
				map.put("verbose", typeVerbose);
				map.put("region", ((Map) e).get("area"));	//地区
				list.add(map);
			}
			return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
}

	
	/**
	 * 贫困村村官和党员情况统计表
	 * **/
	@Override
	public Pager poorVilageOfficialsAndMembers(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {

				// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}

				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";

				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}
				int i = 0;
				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
						
					// 县级查询则统计镇，镇级查询则统计村
					if (((Map) e).get("areaCode").toString().trim().length()>9) {
						areaCode = isCounty 
								? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
								: ((Map) e).get("areaCode").toString().trim();
						System.out.println(((Map) e).get("areaCode").toString().trim());
					}else {
						areaCode = ((Map) e).get("areaCode").toString().trim();
					}
					
					
					laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region, SUM(po.officer) as  officer ,SUM(po.party) as  party ,"
		 +"(SELECT COUNT(p.nature) FROM PoorVillage  p WHERE p.isDel = '0' and p.type = '0'"+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.cry = po.cry and p.village = po.village")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS totalPV,"
		 +"(SELECT COUNT(p.officer) FROM PoorVillage  p WHERE p.officer <> '0' and p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.cry = po.cry and p.village = po.village")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS officials,"
		+"(SELECT COUNT(p.party) FROM PoorVillage  p WHERE p.party <> '0' and p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.cry = po.cry and p.village = po.village")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS members )"
			+ " FROM PoorVillage as po WHERE 1 = 1 and  po.isDel = '0' and po.type = '0' and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.area.id")+"");
					
					Map map = new LinkedHashMap<>();

					typeVerbose = new HashMap<>();
					typeVerbose.put("totalPV", "0");//总贫困村数
					typeVerbose.put("officer",  "0");//大学生村官数量	
					typeVerbose.put("party","0");//中共党员数量
					typeVerbose.put("officialsP", "0%");//有村官的贫困村.占比
					typeVerbose.put("officials", "0");//有村官的贫困村.村数
					typeVerbose.put("membersp", "0%");//有党员的贫困村.占比
					typeVerbose.put("members", "0");//有党员的贫困村.村数	
					
					for (Object o : laborTypeCount) {
						String officialsP = "0%";
						String membersp = "0%";
						Long totalPV = (Long) ((Long) (((Map) o).get("totalPV"))== null ? 0 : ((Map) o).get("totalPV"));
						if (!totalPV.toString().equals("0")&&StringUtil.isNotBlank(totalPV.toString())) {
						 officialsP = BigDecimal.valueOf((long) ((Map) o).get("officials")).divide(BigDecimal.valueOf((long) ((Map) o).get("totalPV")), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						 membersp = BigDecimal.valueOf((long) ((Map) o).get("members")).divide(BigDecimal.valueOf((long) ((Map) o).get("totalPV")), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						}
						typeVerbose = new HashMap<>();
							//typeVerbose.put("region", ((Map) o).get("region"));	//地区
							typeVerbose.put("totalPV", (((Map) o).get("totalPV"))!=null? ((Map) o).get("totalPV") : "0");//总贫困村数
							typeVerbose.put("officer", (((Map) o).get("officer"))!=null? ((Map) o).get("officer") : "0");//大学生村官数量	
							typeVerbose.put("party",(((Map) o).get("party"))!=null? ((Map) o).get("party") : "0");//中共党员数量
							typeVerbose.put("officialsP", "0%".equals(officialsP) ? "0%" : officialsP);//有村官的贫困村.占比
							typeVerbose.put("officials",(((Map) o).get("officials"))!=null? ((Map) o).get("officials") : "0");//有村官的贫困村.村数
							typeVerbose.put("membersp", "0%".equals(membersp) ? "0%" : membersp);//有党员的贫困村.占比
							typeVerbose.put("members", (((Map) o).get("members"))!=null? ((Map) o).get("members") : "0");//有党员的贫困村.村数	
					}
					map.put("verbose", typeVerbose);
					map.put("region", ((Map) e).get("area"));	//地区
					
					list.add(map);
				}
				return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);

	}

	
	 /**
   	 *  贫困村道路硬化及通班车情况统计表
   	 */
	@Override
	public Pager poorVilageRoad(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

				// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}

				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";

				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}
				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
						
					// 县级查询则统计镇，镇级查询则统计村
					if (((Map) e).get("areaCode").toString().trim().length()>9) {
						areaCode = isCounty 
								? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
								: ((Map) e).get("areaCode").toString().trim();
						System.out.println(((Map) e).get("areaCode").toString().trim());
					}else {
						areaCode = ((Map) e).get("areaCode").toString().trim();
					}

					
					laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region,"
			+"(SELECT COUNT(p.nature) FROM PoorVillage  p WHERE p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS totalPV,"
			+"(SELECT COUNT(p.asphalt) FROM PoorVillage p WHERE p.asphalt = '否' and p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS road,"
		 	+"(SELECT COUNT(p.bus) FROM PoorVillage p WHERE p.bus = '否' and p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS bus )"
			+ " FROM PoorVillage as po WHERE 1 = 1 and po.isDel = '0' and po.type = '0' and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.area.id")+"");

					Map map = new LinkedHashMap<>();
					typeVerbose = new HashMap<>();
					typeVerbose.put("totalPV", "0");//总贫困村数
					typeVerbose.put("busP", "0.0%");//未通客运班车数.占比
					typeVerbose.put("bus",  "0");//未通客运班车数.村数
					typeVerbose.put("roadP",  "0.0%");//到乡镇未通沥青（水泥）路.占比	
					typeVerbose.put("road", "0");//到乡镇是否通沥青（水泥）路.村数	
						
					for (Object o : laborTypeCount) {
						String busP = "0.0%";
						String roadP = "0.0%";
						Long totalPV = (Long) ((Long) (((Map) o).get("totalPV"))== null ? 0 : ((Map) o).get("totalPV"));
						if (!totalPV.toString().equals("0")&&StringUtil.isNotBlank(totalPV.toString())) {
						 busP = BigDecimal.valueOf((long) ((Map) o).get("bus")).divide(BigDecimal.valueOf((long) ((Map) o).get("totalPV")), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						 roadP = BigDecimal.valueOf((long) ((Map) o).get("road")).divide(BigDecimal.valueOf((long) ((Map) o).get("totalPV")), 4, BigDecimal.ROUND_HALF_UP)
								.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						}
						
						//typeVerbose.put("region", ((Map) o).get("region"));	//地区
						typeVerbose.put("totalPV", (((Map) o).get("totalPV"))!=null? ((Map) o).get("totalPV") : "0");//总贫困村数
						typeVerbose.put("busP", busP);//未通客运班车数.占比
						typeVerbose.put("bus", (((Map) o).get("bus"))!=null? ((Map) o).get("bus") : "0");//未通客运班车数.村数
						typeVerbose.put("roadP", roadP);//到乡镇未通沥青（水泥）路.占比	
						typeVerbose.put("road", (((Map) o).get("road"))!=null? ((Map) o).get("road") : "0");//到乡镇是否通沥青（水泥）路.村数	
						
					}
					map.put("verbose", typeVerbose);
					map.put("region", ((Map) e).get("area"));	//地区
					list.add(map);
				}
				return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	 /**
 	 *  贫困村饮水情况统计表
 	 */
	@Override
	public Pager poorVilageWater(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

		// 搜索条件
		// 如果没有设置乡镇，默认县下所有乡镇
		String areaCondition = "130434000000";
		boolean isCounty = true;
		// 如果有，验证权限，镇级以上，则查询该镇下所有村
		if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			System.out.println(">>>> 镇级以上");
			areaCondition = country.trim();
			isCounty = false;
		}
		// 如果镇级，则当前用户所在镇下所有村
		if (user.getUserLevel().getLevel() == 4) {
			System.out.println(">>>> 镇级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			isCounty = false;
		}

		String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
			+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
			+ "') order by areaCode";

		// 村级
		if (user.getUserLevel().getLevel() == 5) {
			System.out.println(">>>> 村级。》》");
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			// 重置地区
			grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
				+ areaCondition + "'";
		}
		Map<String, Object> verbose = null;
		Map<String, Object> typeVerbose = null;
		List laborTypeCount = new ArrayList<>();
		List list = new ArrayList<>();
		Long count = 0L;
		String areaCode = "";
		String areaCe = "";
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
				
			// 县级查询则统计镇，镇级查询则统计村
			if (((Map) e).get("areaCode").toString().trim().length()>9) {
				areaCode = isCounty 
						? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
						: ((Map) e).get("areaCode").toString().trim();
				System.out.println(((Map) e).get("areaCode").toString().trim());
			}else {
				areaCode = ((Map) e).get("areaCode").toString().trim();
			}

			
			laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region,  SUM(po.total) as  total ,"
  +"(SELECT sum(p.security) FROM PoorVillage p WHERE   p.type = '0' AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS security,"
  +"(SELECT sum(p.difficulty) FROM PoorVillage p WHERE   p.type = '0'  AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS difficulty )"
  + " FROM PoorVillage as po WHERE 1 = 1 AND po.isDel = '0' and po.type = '0'  and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.village")+"");
			
			Map map = new LinkedHashMap<>();
			typeVerbose = new HashMap<>();
			typeVerbose.put("total", "0");//总贫困村数
			typeVerbose.put("securityP","0.0%");//大学生村官数量	
			typeVerbose.put("security","0");//中共党员数量
			typeVerbose.put("difficultyP", "0.0%");//有村官的贫困村.占比
			typeVerbose.put("difficulty", "0");//有村官的贫困村.村数
			
			for (Object o : laborTypeCount) {
				String securityP = "0.0%";
				String difficultyP = "0.0%";
				String  tl = (String) ((Map) o).get("total")==null?"0":(String) ((Map) o).get("total");
				if (!tl.equals("0")&&StringUtil.isNotBlank(tl))  {
					securityP = BigDecimal.valueOf(((Map) o).get("security")!=null?Long.valueOf(((Map) o).get("security").toString()):0).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
				    difficultyP = BigDecimal.valueOf(((Map) o).get("difficulty")!=null?Long.valueOf(((Map) o).get("difficulty").toString()):0).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
				}
				typeVerbose = new HashMap<>();
				//typeVerbose.put("region", ((Map) o).get("region"));	//地区
				typeVerbose.put("total", (((Map) o).get("total"))!=null? ((Map) o).get("total") : "0");//总户数
				typeVerbose.put("securityP", securityP);//未实现饮水安全户数.占比
				typeVerbose.put("security",  (((Map) o).get("security"))!=null? ((Map) o).get("security") : "0");//未实现饮水安全户数.户数
				typeVerbose.put("difficultyP",difficultyP);//饮水困难户数.占比
				typeVerbose.put("difficulty", (((Map) o).get("difficulty"))!=null? ((Map) o).get("difficulty") : "0");//饮水困难户数.户数
				
			}
			map.put("verbose", typeVerbose);
			map.put("region", ((Map) e).get("area"));	//地区
			list.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	/**
   	 * 贫困村危房改造情况统计表
   	 */
	@Override
	public Pager poorVilageDanger(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

				// 搜索条件
				// 如果没有设置乡镇，默认县下所有乡镇
				String areaCondition = "130434000000";
				boolean isCounty = true;
				// 如果有，验证权限，镇级以上，则查询该镇下所有村
				if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
					System.out.println(">>>> 镇级以上");
					areaCondition = country.trim();
					isCounty = false;
				}
				// 如果镇级，则当前用户所在镇下所有村
				if (user.getUserLevel().getLevel() == 4) {
					System.out.println(">>>> 镇级。》》");
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					isCounty = false;
				}

				String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
					+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
					+ "') order by areaCode";

				// 村级
				if (user.getUserLevel().getLevel() == 5) {
					System.out.println(">>>> 村级。》》");
					Area area = user.getArea();
					areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
					// 重置地区
					grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
						+ areaCondition + "'";
				}
				Map<String, Object> verbose = null;
				Map<String, Object> typeVerbose = null;
				List laborTypeCount = new ArrayList<>();
				List list = new ArrayList<>();
				Long count = 0L;
				String areaCode = "";
				String areaCe = "";
				NumberFormat formatter = NumberFormat.getPercentInstance();
				formatter.setMinimumFractionDigits(2);
				for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
						
					// 县级查询则统计镇，镇级查询则统计村
					if (((Map) e).get("areaCode").toString().trim().length()>9) {
						areaCode = isCounty 
								? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
								: ((Map) e).get("areaCode").toString().trim();
						System.out.println(((Map) e).get("areaCode").toString().trim());
					}else {
						areaCode = ((Map) e).get("areaCode").toString().trim();
					}

					
					laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region,  SUM(po.total) as  total , SUM(po.danger) as danger )"
			+ " FROM PoorVillage as po WHERE 1 = 1 AND  po.isDel = '0' and po.type = '0'  and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.village")+"");
					
					Map map = new LinkedHashMap<>();
					typeVerbose = new HashMap<>();
					typeVerbose.put("total", "0");//总贫困村数
					typeVerbose.put("dangerP",  "0.0%");//大学生村官数量	
					typeVerbose.put("danger","0");//中共党员数量
					
					for (Object o : laborTypeCount) {
						String dangerP = "0.0%";
						String  tl = (String) ((Map) o).get("total")==null?"0":(String) ((Map) o).get("total");
						if (!tl.equals("0")&&StringUtil.isNotBlank(tl))  {
							dangerP = BigDecimal.valueOf(StringUtils.isBlank((String) ((Map) o).get("danger"))?0:Long.valueOf((String) ((Map) o).get("danger"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
									.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
						}
					 
						typeVerbose = new HashMap<>();
						//typeVerbose.put("region", ((Map) o).get("region"));	//地区
						typeVerbose.put("total", (((Map) o).get("total"))!=null? ((Map) o).get("total") : "0");//总户数
						typeVerbose.put("dangerP", dangerP);//危房户数.占比
						typeVerbose.put("danger", (((Map) o).get("danger"))!=null? ((Map) o).get("danger") : "0");//危房户数.户数
						
					}
					map.put("verbose", typeVerbose);
					map.put("region", ((Map) e).get("area"));	//地区
					list.add(map);
				}
				return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	/**
 	 *  贫困村参加社会保险分组情况统计表
 	 */
	@Override
	public Pager poorVilageInsurance(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

					// 搜索条件
					// 如果没有设置乡镇，默认县下所有乡镇
					String areaCondition = "130434000000";
					boolean isCounty = true;
					// 如果有，验证权限，镇级以上，则查询该镇下所有村
					if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
						System.out.println(">>>> 镇级以上");
						areaCondition = country.trim();
						isCounty = false;
					}
					// 如果镇级，则当前用户所在镇下所有村
					if (user.getUserLevel().getLevel() == 4) {
						System.out.println(">>>> 镇级。》》");
						Area area = user.getArea();
						areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
						isCounty = false;
					}

					String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
						+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
						+ "') order by areaCode";

					// 村级
					if (user.getUserLevel().getLevel() == 5) {
						System.out.println(">>>> 村级。》》");
						Area area = user.getArea();
						areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
						// 重置地区
						grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
							+ areaCondition + "'";
					}
					Map<String, Object> verbose = null;
					Map<String, Object> typeVerbose = null;
					List laborTypeCount = new ArrayList<>();
					List list = new ArrayList<>();
					Long count = 0L;
					String areaCode = "";
					String areaCe = "";
					NumberFormat formatter = NumberFormat.getPercentInstance();
					formatter.setMinimumFractionDigits(2);
					for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
							
						// 县级查询则统计镇，镇级查询则统计村
						if (((Map) e).get("areaCode").toString().trim().length()>9) {
							areaCode = isCounty 
									? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
									: ((Map) e).get("areaCode").toString().trim();
							System.out.println(((Map) e).get("areaCode").toString().trim());
						}else {
							areaCode = ((Map) e).get("areaCode").toString().trim();
						}

						
						laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : "po.village")+" as region,  SUM(po.totalNumber) as  total ,"
			 +" SUM(po.medical) as medical ,SUM(po.townPension) as townPension,SUM(po.ruralPension) as ruralPension)"
				+ " FROM PoorVillage as po  WHERE 1 = 1  AND po.isDel = '0' and po.type = '0' and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.village")+"");
						
						Map map = new LinkedHashMap<>();
						typeVerbose = new HashMap<>();
						typeVerbose.put("total", "0");//总户数
						typeVerbose.put("medicalP", "0.0%");//参加新型农村合作医疗人数.占比
						typeVerbose.put("medical",  "0");//参加新型农村合作医疗人数.人数
						typeVerbose.put("townPensionP","0.0%");//参加城镇职工基本养老保险人数.占比
						typeVerbose.put("townPension", "0");//参加城镇职工基本养老保险人数.人数
						typeVerbose.put("ruralPensionP","0.0%");//参加城乡居民基本养老保险人数.占比
						typeVerbose.put("ruralPension",  "0");//参加城乡居民基本养老保险人数.人数
						
						for (Object o : laborTypeCount) {
							String medicalP = "0.0%";
							String townPensionP = "0.0%";
							String ruralPensionP= "0.0%";
							
							String  tl = (String) ((Map) o).get("total")==null?"0":(String) ((Map) o).get("total");
							if (!tl.equals("0")&&StringUtil.isNotBlank(tl))  {
								
								medicalP = BigDecimal.valueOf(StringUtils.isBlank((String) ((Map) o).get("medical"))?0:Long.valueOf((String) ((Map) o).get("medical"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
										.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
								townPensionP = BigDecimal.valueOf(StringUtils.isBlank((String) ((Map) o).get("townPension"))?0:Long.valueOf((String) ((Map) o).get("townPension"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
										.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
								ruralPensionP = BigDecimal.valueOf(StringUtils.isBlank((String) ((Map) o).get("ruralPension"))?0:Long.valueOf((String) ((Map) o).get("ruralPension"))).divide(BigDecimal.valueOf(Long.valueOf((String) ((Map) o).get("total"))), 4, BigDecimal.ROUND_HALF_UP)
										.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
							}
						 
							typeVerbose = new HashMap<>();
							//typeVerbose.put("region", ((Map) o).get("region"));	//地区
							typeVerbose.put("total", (((Map) o).get("total"))!=null? ((Map) o).get("total") : "0");//总户数
							typeVerbose.put("medicalP", medicalP);//参加新型农村合作医疗人数.占比
							typeVerbose.put("medical", (((Map) o).get("medical"))!=null? ((Map) o).get("medical") : "0");//参加新型农村合作医疗人数.人数
							typeVerbose.put("townPensionP",townPensionP);//参加城镇职工基本养老保险人数.占比
							typeVerbose.put("townPension", (((Map) o).get("townPension"))!=null? ((Map) o).get("townPension") : "0");//参加城镇职工基本养老保险人数.人数
							typeVerbose.put("ruralPensionP",ruralPensionP);//参加城乡居民基本养老保险人数.占比
							typeVerbose.put("ruralPension", (((Map) o).get("ruralPension"))!=null? ((Map) o).get("ruralPension") : "0");//参加城乡居民基本养老保险人数.人数
						}
						map.put("verbose", typeVerbose);
						map.put("region", ((Map) e).get("area"));	//地区
						list.add(map);
					}
					return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	/**
 	 *  贫困村特色产业增收情况统计表
 	 */
	@Override
	public Pager poorVilageCharacteristic(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

		// 搜索条件
		// 如果没有设置乡镇，默认县下所有乡镇
		String areaCondition = "130434000000";
		boolean isCounty = true;
		// 如果有，验证权限，镇级以上，则查询该镇下所有村
		if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			areaCondition = country.trim();
			isCounty = false;
		}
		// 如果镇级，则当前用户所在镇下所有村
		if (user.getUserLevel().getLevel() == 4) {
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			isCounty = false;
		}

		String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
			+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
			+ "') order by areaCode";

		// 村级
		if (user.getUserLevel().getLevel() == 5) {
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			// 重置地区
			grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
				+ areaCondition + "'";
		}
		Map<String, Object> verbose = null;
		Map<String, Object> typeVerbose = null;
		List laborTypeCount = new ArrayList<>();
		List list = new ArrayList<>();
		Long count = 0L;
		String areaCode = "";
		String areaCe = "";
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
				
			// 县级查询则统计镇，镇级查询则统计村
			if (((Map) e).get("areaCode").toString().trim().length()>9) {
				areaCode = isCounty 
						? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
						: ((Map) e).get("areaCode").toString().trim();
				System.out.println(((Map) e).get("areaCode").toString().trim());
			}else {
				areaCode = ((Map) e).get("areaCode").toString().trim();
			}
			
			laborTypeCount = this.find("SELECT new map( "+( isCounty ? "po.cry" : " po.village")+" as region,  SUM(po.total) as  total ,SUM(po.cooperative) as cooperative , SUM(po.major) as major,"
	 + "(SELECT COUNT(*) FROM PoorVillage p WHERE  p.isDel = '0' and p.type = '0'  AND  "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+""+(StringUtils.isNotBlank(type) ? " AND p.status="+type : " ")+") as totalPV , "
     +" (SELECT COUNT(*) FROM PoorVillage p WHERE p.cooperative = 1 and p.type = '0'   AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " AND p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS one,"
     +"(SELECT COUNT(*) FROM PoorVillage p WHERE p.cooperative >= 2 and p.type = '0'   AND p.isDel = '0'  "+(StringUtils.isNotBlank(type) ? " AND p.status="+type : " ")+"  AND p.cooperative <= 5 AND "+( isCounty ? "p.cry = po.cry" : "  p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS two,"
     +"(SELECT COUNT(*) FROM PoorVillage p WHERE p.cooperative > 5 and p.type = '0'    AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " AND p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+") AS five,"
     +"(SELECT COUNT(*) FROM PoorVillage p WHERE p.cooperative='0' and p.type = '0'   AND p.isDel = '0' "+(StringUtils.isNotBlank(type) ? " AND p.status="+type : " ")+" AND "+( isCounty ? "p.cry = po.cry" : " p.area.id = po.area.id ")+""+ (StringUtils.isNotBlank(year) ? " and p.annual='" + year.trim() + "'" : " ")+")AS zero)"
	 + " FROM PoorVillage as po WHERE 1 = 1 AND po.isDel = '0' and po.type = '0'   and po.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and po.status="+type : " ")+""+ (StringUtils.isNotBlank(year) ? " and po.annual='" + year.trim() + "'" : " ")+" group by "+( isCounty ? "po.cry" : "po.area.id")+"");
		
			Map map = new LinkedHashMap<>();
			typeVerbose = new HashMap<>();
			typeVerbose.put("totalPV", "0");//总贫困村数
			typeVerbose.put("total", "0");//总户数
			typeVerbose.put("cooperative", "0");//农民专业合作社个数
			typeVerbose.put("zero", "0");//无农民专业合作社贫困村.村数
			typeVerbose.put("zeroP","0.0%");//无农民专业合作社贫困村.占比
			typeVerbose.put("one", "0");//有1个农民专业合作社贫困村.村数
			typeVerbose.put("oneP","0.0%");//有1个农民专业合作社贫困村.占比
			typeVerbose.put("two", "0");//有2个-5个农民专业合作社贫困村.村数
			typeVerbose.put("twoP","0.0%");//有2个-5个农民专业合作社贫困村.占比
			typeVerbose.put("five", "0");//有5个以上农民专业合作社贫困村.村数
			typeVerbose.put("fiveP","0.0%");//有5个以上农民专业合作社贫困村.占比
			typeVerbose.put("major", "0");//参加农民专业合作社贫困户.户数
			typeVerbose.put("majorP","0.0%");//参加农民专业合作社贫困户.占比
			
			for (Object o : laborTypeCount) {
				String zeroP = "0.0%";
				String oneP = "0.0%";
				String twoP= "0.0%";
				String fiveP = "0.0%";
				String majorP= "0.0%";
				Long totalPV = (Long) ((Long) (((Map) o).get("totalPV"))== null ? 0 : ((Map) o).get("totalPV"));
				if (!totalPV.toString().equals("0")&&StringUtil.isNotBlank(totalPV.toString())) {
					zeroP = BigDecimal.valueOf(((Map) o).get("zero").toString()!=null?Long.valueOf(((Map) o).get("zero").toString()):0).divide( BigDecimal.valueOf(Long.valueOf(((Map) o).get("totalPV").toString())), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					oneP = BigDecimal.valueOf(((Map) o).get("one").toString()!=null?Long.valueOf(((Map) o).get("one").toString()):0).divide( BigDecimal.valueOf(Long.valueOf(((Map) o).get("totalPV").toString())), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					twoP = BigDecimal.valueOf(((Map) o).get("two").toString()!=null?Long.valueOf(((Map) o).get("two").toString()):0).divide( BigDecimal.valueOf(Long.valueOf(((Map) o).get("totalPV").toString())), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
					fiveP = BigDecimal.valueOf(((Map) o).get("five").toString()!=null?Long.valueOf(((Map) o).get("five").toString()):0).divide( BigDecimal.valueOf(Long.valueOf(((Map) o).get("totalPV").toString())), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
				}
				String  tl = StringUtil.isNotBlank(((Map) o).get("total").toString())?((Map) o).get("total").toString():"0";
				if (!tl.equals("0")&&StringUtil.isNotBlank(tl)) {
					String major = (StringUtil.isNotBlank((String) ((Map) o).get("major"))?((Map) o).get("major").toString():"0");
					majorP = BigDecimal.valueOf(Long.valueOf(major)).divide( BigDecimal.valueOf(Long.valueOf(tl)), 4, BigDecimal.ROUND_HALF_UP)
							.multiply(BigDecimal.valueOf(100)).doubleValue()+"%";
				}
				typeVerbose = new HashMap<>();
				//typeVerbose.put("region", ((Map) o).get("region"));	//地区
				typeVerbose.put("totalPV", (((Map) o).get("totalPV"))!=null? ((Map) o).get("totalPV") : "0");//总贫困村数
				typeVerbose.put("total", (((Map) o).get("total"))!=null? ((Map) o).get("total") : "0");//总户数
				typeVerbose.put("cooperative", (((Map) o).get("cooperative"))!=null? ((Map) o).get("cooperative") : "0");//农民专业合作社个数
				typeVerbose.put("zero", (((Map) o).get("zero"))!=null? ((Map) o).get("zero") : "0");//无农民专业合作社贫困村.村数
				typeVerbose.put("zeroP",zeroP);//无农民专业合作社贫困村.占比
				typeVerbose.put("one", (((Map) o).get("one"))!=null? ((Map) o).get("one") : "0");//有1个农民专业合作社贫困村.村数
				typeVerbose.put("oneP",oneP);//有1个农民专业合作社贫困村.占比
				typeVerbose.put("two", (((Map) o).get("two"))!=null? ((Map) o).get("two") : "0");//有2个-5个农民专业合作社贫困村.村数
				typeVerbose.put("twoP",twoP);//有2个-5个农民专业合作社贫困村.占比
				typeVerbose.put("five", (((Map) o).get("five"))!=null? ((Map) o).get("five") : "0");//有5个以上农民专业合作社贫困村.村数
				typeVerbose.put("fiveP",fiveP);//有5个以上农民专业合作社贫困村.占比
				typeVerbose.put("major", (((Map) o).get("major"))!=null? ((Map) o).get("major") : "0");//参加农民专业合作社贫困户.户数
				typeVerbose.put("majorP",majorP);//参加农民专业合作社贫困户.占比
			}
			map.put("verbose", typeVerbose);
			map.put("region", ((Map) e).get("area"));	//地区
			list.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}

	/**
	 * 贫困人口净减少统计表excel导出
	 */
	@Override
	public Pager poordecreaseofPopulation(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		// 搜索条件
		// 如果没有设置乡镇，默认县下所有乡镇
		String areaCondition = "130434000000";
		boolean isCounty = true;
		// 如果有，验证权限，镇级以上，则查询该镇下所有村
		if (user.getUserLevel().getLevel() < 4 && StringUtils.isNotBlank(country)) {
			areaCondition = country.trim();
			isCounty = false;
		}
		// 如果镇级，则当前用户所在镇下所有村
		if (user.getUserLevel().getLevel() == 4) {
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			isCounty = false;
		}

		String grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) "
			+ " from Area as a where a.fatherId = (select a.id from Area as a where a.areaCode = '" + areaCondition
			+ "') order by areaCode";

		// 村级
		if (user.getUserLevel().getLevel() == 5) {
			Area area = user.getArea();
			areaCondition = areaCondition == null ? "none" : area.getAreaCode().trim();
			// 重置地区
			grade = "select new Map(a.id as id, a.area as area, a.areaCode as areaCode) from Area as a where a.areaCode ='"
				+ areaCondition + "'";
		}

		Map<String, Object> verbose = null;
		Map<String, Object> typeVerbose = null;
		List laborTypeCount = new ArrayList<>();
		List list = new ArrayList<>();
		String now = StringUtils.isNotBlank(year) ? year : ApplicationProperties.getAnnual();
		Long count = 0L;
		String areaCode = "";
		String areaCe = "";
		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);
		for (Object e : findPageObjects(grade,pageNo,pageSize,new HashMap<>(0))) {
				
			// 县级查询则统计镇，镇级查询则统计村
			areaCode = isCounty 
					? ((Map) e).get("areaCode").toString().trim().substring(0, 9) + "%"
					: ((Map) e).get("areaCode").toString().trim();
			//上年
			Integer Lastyear = Integer.valueOf(now) - 1;
			String la = Lastyear.toString();
			//当年
			String th = now;
			laborTypeCount = this.find("SELECT new map( "+( isCounty ? "p.cry" : "p.village")+" as region,"
					+ "(SELECT SUM(po.poorNumber) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+la+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+") AS a ,"
					+ "(SELECT SUM(po.poorNumber) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+th+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+") AS b ,"
					+ "((SELECT SUM(po.poorNumber) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+la+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+")-(SELECT SUM(po.poorNumber) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+th+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+")) AS c ,"
					+ "(SELECT SUM(po.poor) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+la+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+") AS d ,"
					+ "(SELECT SUM(po.poor) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+th+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+") AS e ,"
					+ "((SELECT SUM(po.poor) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+la+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+")-(SELECT SUM(po.poor) FROM PoorVillage po WHERE po.isDel='0' and po.type = '0' and po.annual = '"+th+"' AND "+( isCounty ? "p.cry = po.cry" : "p.village = po.village")+")) AS f )"
					+ " FROM PoorVillage as p WHERE p.isDel='0' and p.type = '0' and p.area.areaCode like '" + areaCode + "'"+(StringUtils.isNotBlank(type) ? " and p.status="+type : " ")+" group by "+( isCounty ? "p.cry" : "p.village")+"");
			Map map = new LinkedHashMap<>();
			typeVerbose = new HashMap<>();
			typeVerbose.put("lastnumber", "0");//上年贫困人数
			typeVerbose.put("thatnumber","0");//当年贫困人数
			typeVerbose.put("reduce","0");//净减少人数
			typeVerbose.put("lasthouseholds","0");//上年贫困户数
			typeVerbose.put("thathouseholds","0");//当年贫困户数
			typeVerbose.put("reducehouseholds","0");//净减少户数
			for (Object o : laborTypeCount) {
				typeVerbose.put("lastnumber", (((Map) o).get("a"))!=null? ((Map) o).get("a") : "0");//上年贫困人数
				typeVerbose.put("thatnumber", (((Map) o).get("b"))!=null? ((Map) o).get("b") : "0");//当年贫困人数
				typeVerbose.put("reduce",(((Map) o).get("c"))!=null? ((Map) o).get("c") : "0");//净减少人数
				typeVerbose.put("lasthouseholds",(((Map) o).get("d"))!=null? ((Map) o).get("d") : "0");//上年贫困户数
				typeVerbose.put("thathouseholds",(((Map) o).get("e"))!=null? ((Map) o).get("e") : "0");//当年贫困户数
				typeVerbose.put("reducehouseholds",(((Map) o).get("f"))!=null? ((Map) o).get("f") : "0");//净减少户数
				
			}
			map.put("verbose", typeVerbose);
			map.put("region", ((Map) e).get("area"));	//地区
			list.add(map);
		}
		return new Pager(pageSize,pageNo,queryList(grade,null).size(),list);
	}


	/**
	 * 贫困村年人均收入分组情况统计
	 */
	@Override
	public Pager perIncomeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {

        boolean isCountry = StringUtils.isNotBlank(country);
        Integer level = user.getUserLevel().getLevel();
		Area area = user.getArea();

        String areaCondition = "";
        String statusCondition = StringUtils.isNotBlank(status) ? " and pv.STATUS='" + status + "'" : "";
        String yearCondition = StringUtils.isNotBlank(year) ? " and pv.ANNUAL='" + year +"'" : "";

		//////////
        //按乡镇查
        if(isCountry) {
            List result = new ArrayList<>();
//            if(level > LevelUtil.COUNTY) {
//                if(area == null || !country.equalsIgnoreCase(area.getAreaCode())) {
//                    return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
//                }
//            }
            String fragment = " from (select a.id,a.area,a.areaCode from area as a where fatherId in( select id from area where areaCode ='"+ country +"')) as t " +
                    " left join poor_village as pv on pv.area_id=t.id and pv.is_del='0' "+yearCondition + status;
            String sql = "select t.area as area,if(pv.type='0',1,0) as type, ifnull(pv.farmer,0) as farmer " + fragment ;
            String count =" select count(*) " + fragment;
            Integer rows = ((BigInteger)getCurrentSession().createSQLQuery(count).uniqueResult()).intValue();
            List<Map> list = getCurrentSession().createSQLQuery(sql).setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

            for(Map e : list) {
                Map<Object, Object> map = Collections.generateHashMap(
                        "area", e.get("area"),
                        "countVillage", e.get("type"),
                        "c", "0",
                        "c1", "0",
                        "c2", "0",
                        "c3", "0",
                        "c4", "0",
                        "c5", "0",
                        "percentC", "0%",
                        "percentC1", "0%",
                        "percentC2", "0%",
                        "percentC3", "0%",
                        "percentC4", "0%",
                        "percentC5", "0%"
                        );
                double farmer = new Double(e.get("farmer").toString());
                if(farmer<1000 && !"0".equalsIgnoreCase(e.get("type").toString())) {map.put("c1","1");map.put("percentC1","100%");}
                if(farmer<2000 && farmer >=1000){ map.put("c2","1");map.put("percentC2","100%");}
                if(farmer<3000 && farmer >=2000) {map.put("c3","1");map.put("percentC3","100%");}
                if(farmer<4000 && farmer >=3000) {map.put("c4","1");map.put("percentC4","100%");}
                if(farmer<5000 && farmer >=4000) {map.put("c5","1");map.put("percentC5","100%");}
                if(farmer >= 5000) {map.put("c","1");map.put("percentC","100%");}

                result.add(map);
            }
            return new Pager(pageSize,pageNo,rows,result);
        }

		StringBuilder getArea = new StringBuilder( " from Area as a where a.fatherId=3 and a.areaCode like '"
				+ (country != null && country.length() > 8 ? country.substring(0, 9) +"%" : "%") +"' ");
		
		if(StringUtils.isNotBlank(country)) {
			List list  = find(getArea.toString());
			if(!list.isEmpty()) {
				areaCondition += " and r1.areaId=" + ((Area)list.get(0)).getId();
			}
		}
		
		//用户权限错误
		if(level >= LevelUtil.TOWN && area == null) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}
		if(level == LevelUtil.TOWN) {
			List list  = find(getArea + " and a.areaCode like '"
				+ (area.getAreaCode().length() > 8 ? area.getAreaCode().substring(0, 9) +"%" : "none") +"' ");
			if(!list.isEmpty()) {
				areaCondition += " and r1.areaId=" + ((Area)list.get(0)).getId();
			} else {
				areaCondition += "  and r1.areaId='none'";
			}
		}
		StringBuilder sql = new StringBuilder("SELECT t.id as id,t.area as area,"
			+ " ifnull(t.c1,0) as c1, ifnull(CONCAT(cast(t.c1/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC1,"
			+ " ifnull(t.c2,0) as c2,ifnull(CONCAT(cast(t.c2/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC2,"
			+ " ifnull(t.c3,0) as c3,ifnull(CONCAT(cast(t.c3/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC3,"
			+ " ifnull(t.c4,0) as c4,ifnull(CONCAT(cast(t.c4/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC4,"
			+ " ifnull(t.c5,0) as c5, ifnull(CONCAT(cast(t.c5/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC5,"
			+ " ifnull(t.c,0) as c , ifnull(CONCAT(cast(t.c/tv.countVillage*100 as DECIMAL(22,2)),'%'),'0%') as percentC,"
			+ " tv.countvillage as countVillage"
			+ " FROM (SELECT a.fatherId as fatherId, count(1) as countVillage FROM poor_village as pv, area as a where pv.IS_DEL='0' and pv.type='0' and a.id=pv.AREA_ID "+ statusCondition + yearCondition +"  AND pv.TYPE='0' GROUP BY a.fatherId) as tv "
			+ " RIGHT OUTER JOIN"
			+ " (SELECT r1.areaId as id, r1.area as area,r1.c1 as c1, r2.c1 as c2,r3.c1 as c3,r4.c1 as c4,r5.c1 as c5,r.c1 as c FROM"
			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv , area as a where pv.IS_DEL='0'  and pv.type='0'  "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND ifnull(pv.FARMER,0) BETWEEN 0 and 999.99 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 ) as r1,"

			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv , area as a where pv.IS_DEL='0'  and pv.type='0'  "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND pv.FARMER BETWEEN 1000 and 1999.99 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 ) as r2,"

			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv  , area as a where pv.IS_DEL='0'  and pv.type='0' "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND pv.FARMER BETWEEN 2000 and 2999.99 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 )  as r3,"
			
			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv , area as a where pv.IS_DEL='0'  and pv.type='0'  "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND pv.FARMER BETWEEN 3000 and 3999.99 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 )  as r4,"

			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv  , area as a where pv.IS_DEL='0' and pv.type='0' "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND pv.FARMER BETWEEN 4000 and 4999.99 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 )  as r5,"

			+ " (SELECT a3.area as area, a3.id as areaId, t11.c1 as c1 FROM area as a3 LEFT JOIN"
			+ " (SELECT a1.fatherId as fatherId ,t1.c1 as c1  from area as a1 LEFT OUTER JOIN "
			+ " (SELECT a.fatherId as fatherId, count(1) as c1 FROM poor_village as pv  , area as a where pv.IS_DEL='0'  and pv.type='0' "+ statusCondition + yearCondition +" AND pv.AREA_ID=a.ID  AND pv.FARMER >= 5000 GROUP BY a.fatherId) as t1"
			+ " ON t1.fatherId = a1.fatherId GROUP BY a1.fatherId HAVING a1.fatherId > 3) as t11 ON t11.fatherId = a3.id WHERE a3.fatherId=3 )  as r"

			+ " WHERE r1.areaId = r2.areaId AND r3.areaId=r1.areaId AND r4.areaId=r1.areaId AND r5.areaId=r1.areaId AND r.areaId=r1.areaId "+ areaCondition +") as t ON tv.fatherId = t.id ");
		
		Query query = getCurrentSession().createSQLQuery(sql.toString());
		query.setFirstResult((pageNo - 1) * pageSize);
		query.setMaxResults(pageSize);
		List<Map> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		
		for(Map e : list) {
			if(e.get("countVillage") == null) {
				e.put("countVillage", 0) ;
			}
		}
		
		return new Pager(pageSize,pageNo,getCurrentSession().createSQLQuery(sql.toString()).list().size(),list);
	}

	/**
	 * 贫困村村集体经济收入分组情况统计
	 */
	@Override
	public Pager villageIncomeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		Integer level = user.getUserLevel().getLevel();
		Area area = user.getArea();
		// 用户权限错误
		if (level >= LevelUtil.TOWN && area == null) {
			return new Pager(pageNo, pageSize, 0, new ArrayList<>(0));
		}

		List list = null;
		String areaCondition = "";
		String statusCondition = StringUtils.isNotBlank(status) ? " and pv.status='" + status + "'" : "";
		String yearCondition = StringUtils.isNotBlank(year) ? " and pv.annual='" + year + "'" : "";

		boolean isCountry = StringUtils.isNotBlank(country);
        //////////
        //按乡镇查
        if(isCountry) {
            List result = new ArrayList<>();
//            if(level > LevelUtil.COUNTY) {
//                if(area == null || !country.equalsIgnoreCase(area.getAreaCode())) {
//                    return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
//                }
//            }
            String fragment = " from (select a.id,a.area,a.areaCode from area as a where fatherId in( select id from area where areaCode ='"+ country +"')) as t " +
                    " left join poor_village as pv on pv.area_id=t.id and pv.is_del='0' "+yearCondition + status;
            String sql = "select t.area as area,if(pv.type='0',1,0) as type,ifnull(pv.COLLECTIVE,'0') as collective " + fragment ;
            String count =" select count(*) " + fragment;
            Integer rows = ((BigInteger)getCurrentSession().createSQLQuery(count).uniqueResult()).intValue();
            List<Map> queryList = getCurrentSession().createSQLQuery(sql).setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

            for(Map e : queryList) {
                Map<Object, Object> map = Collections.generateHashMap(
                        "area", e.get("area"),
                        "countVillage", e.get("type"),
                        "collectiveAVG", e.get("collective"),
                        "c", "0",
                        "c1", "0",
                        "c2", "0",
                        "c3", "0",
                        "c4", "0",
                        "c5", "0",
                        "percentC", "0%",
                        "percentC1", "0%",
                        "percentC2", "0%",
                        "percentC3", "0%",
                        "percentC4", "0%",
                        "percentC5", "0%"
                );
                double collective = new Double(e.get("collective").toString());
                if(collective== 0 && !"0".equalsIgnoreCase(e.get("type").toString())) {map.put("collectiveAVG","0"); map.put("c","1");map.put("percentC","100%");}
                if(collective<50 && collective > 0 && !"0".equalsIgnoreCase(e.get("type").toString())) {map.put("c1","1");map.put("percentC1","100%");}
                if(collective<100 && collective >=50){ map.put("c2","1");map.put("percentC2","100%");}
                if(collective<200 && collective >=100) {map.put("c3","1");map.put("percentC3","100%");}
                if(collective<300 && collective >=200) {map.put("c4","1");map.put("percentC4","100%");}
                if(collective >= 300) {map.put("c5","1");map.put("percentC5","100%");}

                result.add(map);
            }
            return new Pager(pageSize,pageNo,rows,result);
        }

		StringBuilder getArea = new StringBuilder(" from Area as a where a.fatherId=3 and a.areaCode like '"
			+ (country != null && country.length() > 8 ? country.substring(0, 9) + "%" : "%") + "' ");
		list = find(getArea.toString());
		if (StringUtils.isNotBlank(country)) {
			list = find(getArea.toString());
			if (!list.isEmpty()) {
				areaCondition += " and a.id =" + ((Area) list.get(0)).getId();
			}
		}

		if (level == LevelUtil.TOWN) {
			list = find(getArea + " and a.areaCode like '"
				+ (area.getAreaCode().length() > 8 ? area.getAreaCode().substring(0, 9) + "%" : "none") + "' ");
			if (!list.isEmpty()) {
				areaCondition += " and a.id =" + ((Area) list.get(0)).getId();
			} else {
				areaCondition += "  and a.id='none'";
			}
		}
		String fragment = "(SELECT count(1) as c1 FROM poor_village as pv , area as a1 where pv.IS_DEL='0' and pv.TYPE='0'  AND pv.AREA_ID=a1.ID AND a1.fatherId=a.id "
			+ statusCondition + yearCondition;
		StringBuilder sql = new StringBuilder("SELECT a.id as id,a.area as area ," + fragment
			+ " AND pv.COLLECTIVE > 0 AND pv.COLLECTIVE < 50 GROUP BY a1.fatherId ) as c1," + fragment
			+ " AND pv.COLLECTIVE >= 50 AND pv.COLLECTIVE < 100 GROUP BY a1.fatherId ) as c2," + fragment
			+ " AND pv.COLLECTIVE >= 100 AND pv.COLLECTIVE < 200 GROUP BY a1.fatherId ) as c3," + fragment
			+ " AND pv.COLLECTIVE >= 200 AND pv.COLLECTIVE < 300 GROUP BY a1.fatherId ) as c4," + fragment
			+ " AND pv.COLLECTIVE >= 300 GROUP BY a1.fatherId ) as c5," + fragment
			+ " AND (pv.COLLECTIVE = 0 OR pv.COLLECTIVE ='' OR pv.COLLECTIVE IS NULL) GROUP BY a1.fatherId ) as c,"
			+ "(SELECT count(1) as countVillage FROM poor_village as pv, area as a1 where pv.IS_DEL='0'  and pv.type='0' and a1.id=pv.AREA_ID "
			+ statusCondition + yearCondition + " AND a1.fatherId=a.id AND pv.TYPE='0' GROUP BY a.fatherId) as countVillage"
			+ " from area as a WHERE a.fatherId=3 " + areaCondition);

		Query query = getCurrentSession().createSQLQuery(sql.toString());
		query.setFirstResult((pageNo - 1) * pageSize);
		query.setMaxResults(pageSize);
		List<Map> result = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

		List<Map> totalCollectives = find(
			"SELECT new Map(pv.area.fatherId as fatherId, sum(pv.collective) as totalCollective) FROM PoorVillage as pv where pv.isDel='0' and pv.type='0' "+ yearCondition + statusCondition +" group by pv.area.fatherId");

		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);

		// 计算村均集体收入
		Map temp = new HashMap<>();
		for (Map e : totalCollectives) {
			temp.put((Long) e.get("fatherId"), e.get("totalCollective"));
		}
		for (Map e : result) {
			// 计算村均集体收入
			String str = (String) temp.get(((BigInteger) e.get("id")).longValue());
			BigInteger big = (BigInteger) e.get("countVillage");
			if(big == null) e.put("countVillage", 0);
			Double totalCollective = Double.valueOf(StringUtils.isNotBlank(str) ? str : "0");
			e.put("collectiveAVG",
				totalCollective == null || big == null ? "0.00" : String.format("%.2f", totalCollective / big.longValue()));

			// 分组占比计算
			BigInteger c = (BigInteger) e.get("c");
			BigInteger c1 = (BigInteger) e.get("c1");
			BigInteger c2 = (BigInteger) e.get("c2");
			BigInteger c3 = (BigInteger) e.get("c3");
			BigInteger c4 = (BigInteger) e.get("c4");
			BigInteger c5 = (BigInteger) e.get("c5");
			e.put("percentC", c == null || big == null ? "0%" : String.format("%.2f",c.doubleValue() / big.doubleValue() * 100)+ "%") ;
			e.put("percentC1", c1 == null || big == null ? "0%" : String.format("%.2f",c1.doubleValue() / big.doubleValue() * 100)+ "%");
			e.put("percentC2", c2 == null || big == null ? "0%" : String.format("%.2f",c2.doubleValue() / big.doubleValue() * 100)+ "%");
			e.put("percentC3", c3 == null || big == null ? "0%" : String.format("%.2f",c3.doubleValue() / big.doubleValue() * 100)+ "%");
			e.put("percentC4", c4 == null || big == null ? "0%" : String.format("%.2f",c4.doubleValue() / big.doubleValue() * 100)+ "%");
			e.put("percentC5", c5 == null || big == null ? "0%" : String.format("%.2f",c5.doubleValue() / big.doubleValue() * 100)+ "%");
			
			e.put("c",c == null ? 0 : c);
			e.put("c1",c1 == null ? 0 : c1);
			e.put("c2",c2 == null ? 0 : c2);
			e.put("c3",c3 == null ? 0 : c3);
			e.put("c4",c4 == null ? 0 : c4);
			e.put("c5",c5 == null ? 0 : c5);
		}
		return new Pager(pageSize, pageNo, list.size(), result);
	}

	/**
	 * 贫困村未脱贫人口情况统计
	 */
	@Override
	public Pager wtpStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {

		Integer level = user.getUserLevel().getLevel();
		Area area = user.getArea();
		// 用户权限错误
		if (level >= LevelUtil.TOWN && area == null) {
			return new Pager(pageNo, pageSize, 0, new ArrayList<>(0));
		}

		List list = null;
		String areaCondition = "";
		String statusCondition = StringUtils.isNotBlank(status) ? " and pv.STATUS='" + status + "'" : "";
		String yearCondition = StringUtils.isNotBlank(year) ? " and pv.ANNUAL='" + year + "'" : "";

		boolean isCountry = StringUtils.isNotBlank(country);
        //////////
        //按乡镇查
        if(isCountry) {
            List result = new ArrayList<>();
//            if(level > LevelUtil.COUNTY) {
//                if(area == null || !country.equalsIgnoreCase(area.getAreaCode())) {
//                    return new Pager(pageSize,pageNo,0,new ArrayList<>(0));
//                }
//            }
            String fragment = " from (select a.id,a.area,a.areaCode from area as a where fatherId in( select id from area where areaCode ='"+ country +"')) as t " +
                    " left join poor_village as pv on pv.area_id=t.id and pv.is_del='0' "+yearCondition + status;
            String sql = "select t.area as area,ifnull(pv.type,'0') as type," +
                    " ifnull(pv.total,0) as countF,ifnull(pv.total_number,0) as countP, " +
                    " ifnull(pv.poor,0) as countPF,ifnull(pv.poor_number,0) as countPP " + fragment ;
            String count =" select count(*) " + fragment;
            Integer rows = ((BigInteger)getCurrentSession().createSQLQuery(count).uniqueResult()).intValue();
            List<Map> queryList = getCurrentSession().createSQLQuery(sql).setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

            for (Map e : queryList) {
                boolean isPoorVillage = "0".equals(e.get("type"));
                Map<Object, Object> map = Collections.generateHashMap(
                        "area", e.get("area"),
                        "countF", e.get("countF"),
                        "countP", e.get("countP"),
                        "countPF", isPoorVillage ? e.get("countPF") : "0",
                        "countPP", isPoorVillage ? e.get("countPP") : "0",
                        "countUPF", !isPoorVillage ? e.get("countPF") : "0",
                        "countUPP", !isPoorVillage ? e.get("countPP") : "0",
                        "percentPP", "0%",
                        "percentPF", "0%",
                        "percentUPP", "0%",
                        "percentUPF", "0%"
                        );

                double countF = new Double(map.get("countF").toString());
                double countP = new Double(map.get("countP").toString());
                double countPP = new Double(map.get("countPP").toString());
                double countPF = new Double(map.get("countPF").toString());
                double countUPF = new Double(map.get("countUPF").toString());
                double countUPP = new Double(map.get("countUPP").toString());
                if (!(0 == countF) && !(0==countPF)) {
                    map.put("percentPF", String.format("%.2f",countPF / countF * 100) + "%");
                }
                if (!(0 == countP) && !(0==countPP)) {
                    map.put("percentPP", String.format("%.2f",countPP / countP * 100) + "%");
                }
                if (!(0 == countF) && !(0==countUPF)) {
                    map.put("percentUPF", String.format("%.2f",countUPF / countF * 100) + "%");
                }
                if (!(0 == countP) && !(0==countUPP)) {
                    map.put("percentUPP", String.format("%.2f",countUPP / countP * 100) + "%");
                }
                result.add(map);
            }
            return new Pager(pageSize,pageNo,rows,result);
        }

		StringBuilder getArea = new StringBuilder(" from Area as a where a.fatherId=3 and a.areaCode like '"
			+ (country != null && country.length() > 8 ? country.substring(0, 9) + "%" : "%") + "' ");
		list = find(getArea.toString());
		if (StringUtils.isNotBlank(country)) {
			list = find(getArea.toString());
			if (!list.isEmpty()) {
				areaCondition += " and a1.id=" + ((Area) list.get(0)).getId();
			}
		}

		if (level == LevelUtil.TOWN) {
			list = find(getArea + " and a.areaCode like '"
				+ (area.getAreaCode().length() > 8 ? area.getAreaCode().substring(0, 9) + "%" : "none") + "' ");
			if (!list.isEmpty()) {
				areaCondition += " and a1.id =" + ((Area) list.get(0)).getId();
			} else {
				areaCondition += "  and a1.id ='none'";
			}
		}

		StringBuilder sql = new StringBuilder(" SELECT a1.area as area, "
			// 总户数
			+ " (SELECT  COALESCE(sum(pv.total),0) as countF from poor_village as pv,area as a where pv.is_del='0' and pv.AREA_ID=a.ID " + statusCondition + yearCondition
			+ " AND a.fatherId=a1.id GROUP BY a.fatherId) as countF, "
			// 总人数
			+ " (SELECT  COALESCE(sum(pv.TOTAL_NUMBER),0) as countP FROM poor_village as pv ,area as a where pv.is_del='0' AND pv.AREA_ID=a.id AND a.fatherId=a1.id "
			+ statusCondition + yearCondition + "  GROUP BY a.fatherId) as countP, "
			// 贫困村户数
			+ " (SELECT  COALESCE(sum(pv.poor),0) as countPF FROM poor_village as pv, area as a where pv.IS_DEL='0' and a.id=pv.AREA_ID and pv.type='0'  "
			+ statusCondition + yearCondition + " AND a.fatherId=a1.id  GROUP BY a.fatherId) as countPF, "
			// 贫困村人数
			+ " (SELECT  COALESCE(sum(pv.poor_number),0) as countPF FROM poor_village as pv, area as a where  pv.IS_DEL='0' and a.id=pv.AREA_ID and pv.type='0'"
			+ statusCondition + yearCondition + " AND a.fatherId=a1.id  GROUP BY a.fatherId) as countPP, "
			// 非贫困村户数
			+ " (SELECT COALESCE(sum(pv.poor),0) as countUPF FROM poor_village as pv, area as a where pv.IS_DEL='0' and a.id=pv.AREA_ID AND pv.type='1'"
			+ statusCondition + yearCondition + " AND a.fatherId=a1.id  GROUP BY a.fatherId) as countUPF, "
			// 非贫困村人数
			+ " (SELECT COALESCE(sum(pv.poor_number),0) as countUPP FROM poor_village as pv,area as a WHERE pv.IS_DEL='0' AND pv.AREA_ID=a.ID AND pv.type='1'"
			+ statusCondition + yearCondition + " AND a.fatherId=a1.id  GROUP BY a.fatherId) as countUPP "
			+ " FROM area as a1 WHERE a1.fatherId=3 " + areaCondition);

		Query query = getCurrentSession().createSQLQuery(sql.toString());
		query.setFirstResult((pageNo - 1) * pageSize);
		query.setMaxResults(pageSize);
		List<Map> result = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

		NumberFormat formatter = NumberFormat.getPercentInstance();
		formatter.setMinimumFractionDigits(2);

		// 占比计算
		for (Map e : result) {
			// 总户数
			Double countF = (Double) e.get("countF");
			// 总人数
			Double countP = (Double) e.get("countP");
			// 贫困村户数
			Double countPF = (Double) e.get("countPF");
			// 贫困村人数
			Double countPP = (Double) e.get("countPP");
			// 非贫困村户数
			Double countUPF = (Double) e.get("countUPF");
			// 非贫困村人数
			Double countUPP = (Double) e.get("countUPP");
			// 贫困村户数占比
			e.put("percentPF",
				countPF == null || countF == null || countF.equals(0.0) || countPF.equals(0.0)   ? "0%" : String.format("%.2f",countPF / countF * 100) + "%");
			// 贫困村人数占比
			e.put("percentPP",
				countPP == null || countP == null || countP.equals(0.0) || countPP.equals(0.0) ? "0%" : String.format("%.2f",countPP / countP * 100)+ "%");
			// 非贫困村户数占比
			e.put("percentUPF",
				countUPF == null || countF == null || countF.equals(0.0) || countUPF.equals(0.0) ? "0%" : String.format("%.2f",countUPF/ countF * 100)+ "%");
			// 非贫困村人数占比
			e.put("percentUPP",
				countUPP == null || countP == null || countP.equals(0.0) || countUPP.equals(0.0) ? "0%" : String.format("%.2f",countUPP / countP * 100)+ "%");
			
			e.put("countF",countF == null ? 0 : countF);
			e.put("countP",countP == null ? 0 : countP);
			e.put("countPF",countPF == null ? 0 : countPF);
			e.put("countPP",countPP == null ? 0 : countPP);
			e.put("countUPF",countUPF == null ? 0 : countUPF);
			e.put("countUPP",countUPP == null ? 0 : countUPP);
		}
		return new Pager(pageSize, pageNo, list.size(), result);
	}

	@Override
	public Long addPoorVillageHistory(PoorVillageHistory poorVillageHistory) {
		return (Long) getCurrentSession().save(poorVillageHistory);
	}

	@Override
	public PoorVillageHistory getPoorVillageHistory(Long id) {
		
		List result = getCurrentSession().createQuery(" from PoorVillageHistory where id =" + id).list();
		return (PoorVillageHistory) (Collections.isEmpty(result) ? null : result.get(0));
	}

}